https://kotlinlang.org logo
Channels
Powered by
#exposed
Title
# exposed
r

Raphaël K

06/01/2022, 11:19 AM
Hello everyone, is it possible to do common table expression like this example with exposed ?
Copy code
WITH ten_parents AS (SELECT * from parent LIMIT 10)
SELECT *
  FROM ten_parents p
    LEFT JOIN child c
      ON c.parent_id = p.id
The goal here is to limit a join request but only for the parents, as they can have several children 🙂
s

spand

06/01/2022, 11:39 AM
I think you should be able to
Copy code
SELECT *
  FROM (SELECT * from parent LIMIT 10) AS p
    LEFT JOIN child c
      ON c.parent_id = p.id
Isnt that enough ?
👍 1
r

Raphaël K

06/01/2022, 11:41 AM
Yes, it does also the trick 🙂 Is there an easy way with the DSL syntax to do so?
s

spand

06/01/2022, 11:45 AM
Something like
Copy code
tbl.slice(col1).selectAll().limit(10).alias("p")
r

Raphaël K

06/01/2022, 11:53 AM
I will try to do that with my own classes, thanks 🙂
I think I succeeded with your syntax. Here is the result with my own tables:
Copy code
val query = (PlaceTable leftJoin PlaceScheduleTable).selectAll()
      .apply { if (count != null) limit(count, offset) }.alias("p")

    val results = query
      .leftJoin(PlaceImageTable, { PlaceImageTable.place }, { query[PlaceTable.id] })
      .leftJoin(FileTable, { FileTable.id }, { PlaceImageTable.file })
      .selectAll()
Thank you again @spand 🙂
s

spand

06/01/2022, 12:52 PM
Excellent 🙂
Though I cant see why you want to leftjoin in 
query
when you do it later also
r

Raphaël K

06/01/2022, 12:54 PM
That is not the same table, if I do it earlier, I will not have all the images linked to places
👍 1
7 Views
Powered by