ENH: Postgres: Provide API for SELECT DISTINCT ON (<column>, <column>, ...)

[rbygrave]

As per ebean-orm-deprecated/ebean-querybean#70

Provide API to support Postgres query extension SELECT DISTINCT ON (..)

SELECT DISTINCT ON (c.id) 
  c.id, e.date
FROM city c JOIN events e ON c.id = e.city_id
WHERE c.id IN (....)
ORDER BY c.id, e.date DESC

These type of queries run usually a lot faster than the common e.date = (SELECT max(e2.date) ...) subquery solution to find the latest event date per city.

Proposed API

Similar do setDistinct(boolean) Ebean could provide setDistinctOn(<column alias>...), e.g.

var city = QCity.alias();
query.setDistinctOn(city.id) // varargs parameter to support more columns

I don't know if Ebean does query checking, but if it does, then it would be worth a warning if both conditions below are not true for such a query. Otherwise the query result will be unpredictable.

1. columns in DISTINCT ON should match left most columns in ORDER BY
2. number of columns in ORDER BY > number of columns in DISTINCT ON

[rbygrave]

I'm going to close this for now.

[c-classen]

Are there any plans on implementing this in the future?

[AntoineDuComptoirDesPharmacies]

We are interested in this feature too. 👍

[AntoineDuComptoirDesPharmacies]

For @c-classen we found a workaround at the moment.
We add a new attribute to our entity with a @Aggregation annotation.

Ex :

    /**
     * Aggregation used in query to request only one sale offer per product id
     */
    @Aggregation("distinct on (product_id) id")
    private Long distinctOnProductId;

Then, when we want to get entities according to this, we do something like this :

        query.select("distinctOnProductId");
        query = DB.find(query.getBeanType()).where().in("id", query).query();

Hope it will help,
LCDP

[rob-bygrave]

"distinct on (product_id) id"

Can you share the exact sql that is being produced and used for this query?

[rbygrave]

Re-opening and preparing a PR ...

[AntoineDuComptoirDesPharmacies]

"distinct on (product_id) id"

Can you share the exact sql that is being produced and used for this query?

Here is the SQL generated by Ebean :

select t0.id, t0.last_modifier, [...] from ad t0
      where t0.id in (select distinct on (product_id) t0.id from ad t0
            where t0.owner_id = any(ARRAY[8]) 
            order by t0.product_id, t0.created_at desc nulls last)
   order by t0.created_at desc nulls first limit 20;

Note : The inner order by is generated by Ebean because we put order by on both query in our code.

Yours faithfully,
LCDP

[rbygrave]

Please review the test cases in the associated PR and confirm it satisfies your use cases for DISTINCT ON queries.

[AntoineDuComptoirDesPharmacies]

It seems perfect to our eyes.
When 14.2.0 will be out, we will migrate to this new method and will report the result here.

Yours faithfully,
LCDP

[AntoineDuComptoirDesPharmacies]

We have upgraded to the new version of Ebean and switch our code to this new distinctOn method that works like a charm.
Thank you ! 👍