Search code examples
scalascalaquery

Scalaquery generating invalid sql on join onto the same table

I have a query that needs to left join onto the same table to find the latest row of an account. 

val all = for { 
  Join(s,s1) <- Subscriptions leftJoin Subscriptions 
                  on ((a,b) => a.account === b.account &&  a.id < b.id)  
                  if s1.id.?.isNull
} yield s

This generates the following SQL

SELECT `t2`.`ACCOUNT`,`t2`.`PLAN`,`t2`.`CALLBACK`,`t2`.`DELETED`,`t2`.`HIBERNATED`,`t2`.`CREATED`,`t2`.`UPDATED` FROM {oj `SUBSCRIPTION` `t2` left outer join `SUBSCRIPTION` `t2` on ((`t2`.`ID`=`t2`.`ID`) and (`t2`.`ID` < `t2`.`ID`))} WHERE (`t2`.`ID` is null)

The problem is with Subscription t2 left join Subscription t2. The tables needs to have different aliases given to them.

I have gotten around this problem by copy and pasting the object into Subscriptionsand changed the query to 

val all = for { 
  Join(s,s1) <- Subscriptions leftJoin Subscriptions1 
                  on ((a,b) => a.account === b.account &&  a.id < b.id)  
                  if s1.id.?.isNull
} yield s

This query generates valid SQL but is less than ideal.

Is there a way around this? If its fixed in SLICK then will make the jump to scala 2.10.

Solution

  • SLICK 0.11.2 works with this use case. I migrated my project to scala 2.10/play 2.1 for this