Hello everyone, is it possible to do common table ...

# exposed

[Raphaël K]

Hello everyone, is it possible to do common table expression like this example with exposed ?

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 🙂

[spand]

I think you should be able to

SELECT *
  FROM (SELECT * from parent LIMIT 10) AS p
    LEFT JOIN child c
      ON c.parent_id = p.id

Isnt that enough ?

[Raphaël K]

Yes, it does also the trick 🙂
Is there an easy way with the DSL syntax to do so?

[spand]

Something like

tbl.slice(col1).selectAll().limit(10).alias("p")

[Raphaël K]

I will try to do that with my own classes, thanks 🙂

[Raphaël K]

I think I succeeded with your syntax. Here is the result with my own tables:

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()

[Raphaël K]

Thank you again @spand 🙂

[spand]

Excellent 🙂

[spand]

Though I cant see why you want to leftjoin in query when you do it later also

[Raphaël K]

That is not the same table, if I do it earlier, I will not have all the images linked to places