Problem with QueryDSL

[Mikko Tanskanen]

Hi, 

I'm trying to execute the following query: 

withSQL(select(i.id)
  .from(Image as i)
  .where.exists(
    select.from(PurchasedImage as pi)
      .leftJoin(Purchase as p)
        .on(pi.purchaseId, p.id)
      .where.eq(pi.imageId, i.id)
     .and
     .eq(p.userId, userId)
      .and
      .ge(p.validTo, ZonedDateTime.now())
    ))
    .map(Image(i.resultName))
    .list().apply()

But I get: 
Execution exception[[ResultSetExtractorException: Failed to retrieve value because The column name i_on_i was not found in this ResultSet.. If you're using SQLInterpolation, you may mistake u.id for u.resultName.id.]]

At the end, I would like to have the exist result as true/false value, like in this sql: 

select i.d, exists (

select pi.id 

from purchased_image pi 

left join purchase p on p.id = pi.purchase_id

where pi.image_id = i.id and p.valid_to > NOW() and p.user_id = 18) as hasAccess

from image i

But I'm at lost how do I perform this query in scalikejdbc. 

Thanks in advance

[Kazuhiro Sera]

select(i.result.id) should work for you

--
You received this message because you are subscribed to the Google Groups "ScalikeJDBC Users Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalikejdbc-users...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalikejdbc-users-group/949a7025-d6cf-4c05-af44-295eec50fcc2n%40googlegroups.com.

[Mikko Tanskanen]

I tried that, but that gives me: 

 Execution exception[[ResultSetExtractorException: Failed to retrieve value because The column name dfi_on_i was not found in this ResultSet.. If you're using SQLInterpolation, you may mistake u.id for u.resultName.id.]]

[Kazuhiro Sera]

dfi_on_i is not the id column. Probably, is it dxx_fxxx_id?

To view this discussion on the web visit https://groups.google.com/d/msgid/scalikejdbc-users-group/7eb47492-7f53-4723-b10e-b29040407bccn%40googlegroups.com.

[Mikko Tanskanen]

So now I have: 
withSQL(select(i.result.id)

  .from(Image as i)
  .where.exists(
    select.from(PurchasedImage as pi)
      .leftJoin(Purchase as p)
        .on(pi.purchaseId, p.id)
      .where.eq(pi.imageId, i.id)
     .and
     .eq(p.userId, userId)
      .and
      .ge(p.validTo, ZonedDateTime.now())
    ))
    .map(Image(i.resultName))
    .list().apply()  

And I get that dfi_on_i was not found...

These error messages are pretty cryptic, I don't know how to approach this problem. 

[Kazuhiro Sera]

What does Image() method do internally? I guess the method accesses the field dxxFxxId. Also, I recommend enabling query logs http://scalikejdbc.org/documentation/query-inspector.html

To view this discussion on the web visit https://groups.google.com/d/msgid/scalikejdbc-users-group/c72ab93a-50c3-4d12-87ec-250485862877n%40googlegroups.com.

[Mikko Tanskanen]

That query is implemented in Image object, which is generated by scalikejdbcGen. 

Image is a case class, also generated by scalikejdbcGen.

If I "go to implementation" of Image method in the map funcion, it suggests this generated method: 

def apply(i: ResultName[Image])(rs: WrappedResultSet): Image = 
  new Image(
    id = rs.get(i.id), 
    createdAt = rs.get(i.createdAt)...)

What is dxxFxxId field? 

[Kazuhiro Sera]

I don't know what the exact column name is but the table should have dxxxx_fxxxxx_ixxxx column (e.g., device_firmware_id). And the apply method should have corresponding deviceFirmwareId = rs.get(i.deviceFirmwareId) or something like that.

If you're not intentionally using select(i.result.id).from, select.from should work for you.

[Mikko Tanskanen]

Ah, I get it, thanks!

Now that I have that sorted out, I would need the result of 

exists (select pi.id 

  from purchased_image pi 

  left join purchase p on p.id = pi.purchase_id

  where pi.image_id = i.id and p.valid_to > NOW() and p.user_id = ${userId}) as hasAccess

to be a part of the result (see the first post in this thread). Can this be done with QueryDSL? I have now: 

withSQL(select(
  i.result.id,

  i..., (other fields from image)

  select(pi.id).from(PurchasedImage as pi)

    .leftJoin(Purchase as p)
      .on(pi.purchaseId, p.id)

    .where.eq(pi.imageId, i.result.id)

    .and
    .eq(p.userId, userId)
    .and
    .ge(p.validTo, ZonedDateTime.now()))

  .from(Image as i))
  .map(Image(i.resultName))
.  list().apply()

But I get "required SQLSyntax, found ConditionSQLBuilder[Nothing]"
I added hasAccess to Image case class property and apply method (there isn't such column in the table).

Can this be done with queryDSL or should it be done some other way?