Hi everyone, I'm struggling to achieve a Postgres distinct on/order by query using the Exposed DSL.....

# exposed

[Bornyls Deen]

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

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

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.

[Kenneth Wußmann]

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

[Evan R.]

What is withDistinct() doing that is unexpected?

[Evan R.]

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:

DistinctJoin

[Bornyls Deen]

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.

[Bornyls Deen]

The documentation is pretty light on withDistinct() so I might be missing something.

[Evan R.]

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