https://kotlinlang.org logo
#exposed
Title
# exposed
b

Bornyls Deen

05/20/2020, 9:43 AM
Hi everyone, I'm struggling to achieve a Postgres distinct on/order by query using the Exposed DSL... I'm looking to execute something like this
Copy code
select distinct on
    (cats.name) cats
from 
    groups_cats
inner join
    cats
on
    groups_cats.cat_id = cats.id
where
    groups_cats.cat_id = 25
order by 
    cats.name, groups.created desc
I've managed to come up with this
Copy code
GroupsCats
    .join(Cats, JoinType.INNER) { Cats.id eq GroupsCats.catId }
    .select { GroupsCats.catId eq catId }
    .orderBy(Cats.created to SortOrder.DESC)
But I can't figure out how to make the result distinct on a particular column (cat name in this case)... the
withDistinct()
method sounded hopeful but doesn't seem to work in the way that I need. If anyone has any ideas/suggestions I would really appreciate it.
k

Kenneth Wußmann

05/20/2020, 11:41 AM
Hey! My latest state is that it’s still not possible out-of-the-box with exposed but you can easily write this function yourself. This would be an example: https://github.com/JetBrains/Exposed/issues/500#issuecomment-543574151
e

Evan R.

05/20/2020, 12:22 PM
What is withDistinct() doing that is unexpected?
Here’s an example of a join that I’m doing where I’m just selecting the columns of one table in my innerJoin. I use MySQL, but it should be similar:
b

Bornyls Deen

05/20/2020, 1:27 PM
The example from the issue you shared works perfectly @Kenneth Wußmann thank you 🙂 @Evan R. From what I can understand the
withDistinct()
method allows you to grab a single column but not retrieve entire rows with a distinct filter on one column if that makes sense.
👍 1
The documentation is pretty light on
withDistinct()
so I might be missing something.
e

Evan R.

05/20/2020, 1:37 PM
Ah got it, I hadn’t seen “distinct on” before. You may want to read up on this issue then: https://github.com/JetBrains/Exposed/issues/500
👍 1
351 Views