1

I've been trying to add Slick to my app, and everything has been going great until I had to do a left join. Below is the sql query that I'm trying to generate:

SELECT i.id,i.category_id,iab.aspect_ids FROM items i LEFT JOIN item_aspects_binary iab ON i.id=iab.item_id WHERE i.id = 380292547708

Where it's a left join filtered by the primary id for the left table. Here's the for comprehension that I used to generate the Slick Query:

val q = for {
    (i, a) <- items leftJoin itemAspects on (_.id === _.itemId) if (i.id === id)
  } yield (i.id, i.category, a.aspectIds)

Which then generates the following SQL query:

select x2.x3, x2.x4, x5.x6 from (select x7.`id` as x3, x7.`category_id` as x4 from `items` x7) x2 left outer join (select x8.`item_id` as x9, x8.`aspect_ids` as x6 from `item_aspects_binary` x8) x5 on x2.x3 = x5.x9 where x2.x3 = 380292547708

However, this gives the following error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select x2.id, x2.category_id, x3.x4 from items x2, (select x2.id as x5 f' at line 3"

Am I missing something?

Also, why does this generate a subquery?

T_Rex
  • 43
  • 4
  • May be a bug, may have already been fixed. Please indicate which version of Slick and which database you are using. Also, are you sure THIS error message belongs to THIS sql query? They don't seem to relate exactly. – cvogt Jan 17 '14 at 06:53
  • i use slick version 1.0.1 with postgresql 9.3. leftJoin works fine in my case. My code is very similar to your: `val query = for { (e, c) <- Events leftJoin EventCategories on (_.categoryId === _.id) if (e.id === 3L) } yield (e.sysName, e.deleted, c.sysName)` **generated SQL query is**: `select x2.x3, x2.x4, x5.x6 from (select x7."id" as x8, x7."deleted" as x4, x7."category_id" as x9, x7."sys_name" as x3 from "events" x7) x2 left outer join (select x10."id" as x11, x10."sys_name" as x6 from "event_categories" x10) x5 on x2.x9 = x5.x11 where x2.x8 = 3` – Artsiom Miklushou Jan 17 '14 at 09:19

0 Answers0