Record-Squeryl select single column problem

[Steven Yang]

 Hi

I am currently trying out Record-Squeryl and found something weird.

Here is my Record declaration.

class Textbook private() extends Record[Textbook] with KeyedRecord[Long] {

  override def meta = Textbook

  @Column(name="id")

  override val idField = new LongField(this)

  val bookName = new StringField(this, 100)

  val coverPicture = new StringField(this, 200)

}

object Textbook extends Textbook with MetaRecord[Textbook]

here are some of my methods:

1.

//select all textbooks

def getAllTextbooks() = from(KKSchema.textbooks) (b => select(b))

2.

def getFromAllTextbooks(id: Long) =

   //select all subquery part is just trying to make things complicated

   from(getAllTextbooks) (b => where(b.id === id) select(b.id))

3.

//simple direct from table, select id columns only

def getAllTextbookIds() = from(KKSchema.textbooks) (b => select(b.id))

the resulting query looks like:

for query 2:

Select

  q1.Textbook2_id as q1_Textbook2_id,

  q1.Textbook2_id as q1_Textbook2_id

From

  (Select

     Textbook2.bookName as Textbook2_bookName,

     Textbook2.id as Textbook2_id,

     Textbook2.coverPicture as Textbook2_coverPicture

   From

     Textbook Textbook2

  )  q1

Where

  (q1.Textbook2_id = 3)

for query 3:

Select

  Textbook1.id as Textbook1_id,

  Textbook1.id as Textbook1_id

From

  Textbook Textbook1

notice that the column is selected twice

if i change the selected column to bookName

for query 2:

Select

  q1.Textbook2_bookName as q1_Textbook2_bookName,

  q1.Textbook2_bookName as q1_Textbook2_bookName,

  q1.Textbook2_id as q1_Textbook2_id,

  q1.Textbook2_coverPicture as q1_Textbook2_coverPicture

From

  (Select

     Textbook2.bookName as Textbook2_bookName,

     Textbook2.id as Textbook2_id,

     Textbook2.coverPicture as Textbook2_coverPicture

   From

     Textbook Textbook2

  )  q1

Where

  (q1.Textbook2_id = 3)

for query 3:

Select

  Textbook1.bookName as Textbook1_bookName,

  Textbook1.bookName as Textbook1_bookName,

  Textbook1.id as Textbook1_id,

  Textbook1.coverPicture as Textbook1_coverPicture

From

  Textbook Textbook1

notice that all columns are selected with bookName twice

The thing here is that the result is correct. 

I do get a "list" of Long or String depends on the column I selected.

The problem comes when I use these queries as a sub-query in a "in" clause.

The generated SQL will look something like 

...

b.id in (

  Select

    Textbook1.bookName as Textbook1_bookName,

    Textbook1.bookName as Textbook1_bookName,

    Textbook1.id as Textbook1_id,

    Textbook1.coverPicture as Textbook1_coverPicture

  From ....)

And there for giving me a SQL exception.

for the above SQLs if I use none Record class like:
class Textbook(val id: Long, val bookName: String, val coverPicture: String)

generated SQL looks correct only 1 column is in the Select statement.

(I did not test the IN for none Record class. I found this behavior when trying to use IN with Record class)

Thanks

[Michael Gottschalk]

Hi Steven,

thanks for already tracking down the problem so far.

I'll try to reproduce and fix it.

Cheers,
Michael

On 28 Jan., 09:21, Steven Yang <kenshin...@gmail.com> wrote:
>  Hi
> I am currently trying out Record-Squeryl and found something weird.
>
> Here is my Record declaration.
>

> *class Textbook private() extends Record[Textbook] with KeyedRecord[Long] {*
> *  override def meta = Textbook*
> *
> *
> *  @Column(name="id")*
> *  override val idField = new LongField(this)*
> *
> *
> *  val bookName = new StringField(this, 100)*
> *  val coverPicture = new StringField(this, 200)*
> *}*
> *
> *
> *object Textbook extends Textbook with MetaRecord[Textbook]*

>
> here are some of my methods:
>
> 1.

> *//select all textbooks*
> *def getAllTextbooks() = from(KKSchema.textbooks) (b => select(b))*
>
> 2.
> *def getFromAllTextbooks(id: Long) =*
> *   //select all subquery part is just trying to make things complicated*
> *   from(getAllTextbooks) (b => where(b.id === id) select(b.id))*
>
> 3.
> *//simple direct from table, select id columns only*
> *def getAllTextbookIds() = from(KKSchema.textbooks) (b => select(b.id))*

>
> the resulting query looks like:
>

> *for query 2:*
> *Select*
> *  q1.Textbook2_id as q1_Textbook2_id,*
> *  q1.Textbook2_id as q1_Textbook2_id*
> *From*
> *  (Select*
> *     Textbook2.bookName as Textbook2_bookName,*
> *     Textbook2.id as Textbook2_id,*
> *     Textbook2.coverPicture as Textbook2_coverPicture*
> *   From*
> *     Textbook Textbook2*
> *  )  q1*
> *Where*
> *  (q1.Textbook2_id = 3)*
>
> *for query 3:*
>
> *Select*
> *  Textbook1.id as Textbook1_id,*
> *  Textbook1.id as Textbook1_id*
> *From*
> *  Textbook Textbook1*
> *
> *

> notice that the column is selected twice
>
> if i change the selected column to bookName
>

> *for query 2:*
> *Select*
> *  q1.Textbook2_bookName as q1_Textbook2_bookName,*
> *  q1.Textbook2_bookName as q1_Textbook2_bookName,*
> *  q1.Textbook2_id as q1_Textbook2_id,*
> *  q1.Textbook2_coverPicture as q1_Textbook2_coverPicture*
> *From*
> *  (Select*
> *     Textbook2.bookName as Textbook2_bookName,*
> *     Textbook2.id as Textbook2_id,*
> *     Textbook2.coverPicture as Textbook2_coverPicture*
> *   From*
> *     Textbook Textbook2*
> *  )  q1*
> *Where*
> *  (q1.Textbook2_id = 3)*
>
> *for query 3:*
> *Select*
> *  Textbook1.bookName as Textbook1_bookName,*
> *  Textbook1.bookName as Textbook1_bookName,*
> *  Textbook1.id as Textbook1_id,*
> *  Textbook1.coverPicture as Textbook1_coverPicture*
> *From*
> *  Textbook Textbook1*

>
> notice that all columns are selected with bookName twice
>
> The thing here is that the result is correct.
> I do get a "list" of Long or String depends on the column I selected.
>
> The problem comes when I use these queries as a sub-query in a "in" clause.
> The generated SQL will look something like
> ...

> *b.id in (*
> *  Select*
> *    Textbook1.bookName as Textbook1_bookName,*
> *    Textbook1.bookName as Textbook1_bookName,*
> *    Textbook1.id as Textbook1_id,*
> *    Textbook1.coverPicture as Textbook1_coverPicture*
> *  From ....)*
> *
> *

> And there for giving me a SQL exception.
>
> for the above SQLs if I use none Record class like:

> *class Textbook(val id: Long, val bookName: String, val coverPicture:
> String)*
> *
> *

[Michael Gottschalk]

Hi Steven,

I was able to reproduce the problem.

It seems that the implicit conversions defined in RecordTypeMode are
not applied for arguments of the select function.
As a workaround, you can just use the "is" function on the fields in
selects, e.g.:

from(KKSchema.textbooks) (b => select(b.idField.is))
or
from(KKSchema.textbooks) (b => select(b.bookName.is))

I'll see if It can be really fixed somehow.

Can you create a ticket for this problem and document it in the Squery-
Record Wiki page?

Thanks!
Michael

On 29 Jan., 12:07, Michael Gottschalk <migottsch...@googlemail.com>
wrote:

[Steven Yang]

Hi 

thanks for the response

ticket created 

http://www.assembla.com/spaces/liftweb/tickets/876-record-squeryl-selecting-unspecified-columns-in-generated-sql

and wiki added, please modify if necessary.

by the way, because I am in the pre-start phase of a Lift project and wanting to use Squeryl for the main DB support and want to make sure Record can support all the what Squeryl supports and the difference with pure Squeryl if any.

I will be making test cases to make sure I can get Squeryl working at least for every function state on the site.

I can do the equivalent test cases for Record-Squeryl and send you the cases and report any issue if found.

--
You received this message because you are subscribed to the Google Groups "Lift" group.
To post to this group, send email to lif...@googlegroups.com.
To unsubscribe from this group, send email to liftweb+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/liftweb?hl=en.

[Michael Gottschalk]

Hi Steven,

On 29 Jan., 14:47, Steven Yang <kenshin...@gmail.com> wrote:
> Hi

> thanks for the response
>
> ticket createdhttp://www.assembla.com/spaces/liftweb/tickets/876-record-squeryl-sel...

>
> and wiki added, please modify if necessary.

nice, thank you.

> by the way, because I am in the pre-start phase of a Lift project and
> wanting to use Squeryl for the main DB support and want to make sure Record
> can support all the what Squeryl supports and the difference with pure
> Squeryl if any.
>
> I will be making test cases to make sure I can get Squeryl working at least
> for every function state on the site.
>
> I can do the equivalent test cases for Record-Squeryl and send you the cases
> and report any issue if found.

That sounds good. The test cases for squeryl-record are not yet very
comprehensive and I'd be glad to integrate more of them.

Cheers,
Michael

> On Sat, Jan 29, 2011 at 8:22 PM, Michael Gottschalk <
>

> > liftweb+u...@googlegroups.com<liftweb%2Bunsu...@googlegroups.com>
> > .

[Steven Yang]

great i will get on it

but i am still consider new to Scala, Lift, Squeryl. 

I suspect this will take me a while, do my best 

To unsubscribe from this group, send email to liftweb+u...@googlegroups.com.