search

alecdotninja / active_record_distinct_on

Support for `DISTINCT ON` statements when querying with ActiveRecord
MIT License
34 stars 11 forks source link

[FeatureRequest] Support joined associations #22

Open raldred opened 2 years ago

raldred commented 2 years ago

It's currently not possible to use joined associations with distinct_on and order If you do, Postgres will error "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"

So i tried to see if I can include the order expression as well, but it doesn't work... Contrived example...

Article.joins(:author).includes(:author).order(author: :id).distinct_on(:id, author: :id) This generates invalid SQL: SELECT DISTINCT ON ("articles"."{:author=>:id}")

tried Article.joins(:author).includes(:author).distinct_on('"authors"."id"') This generates invalid SQL also SELECT DISTINCT ON ("articles"."""authors"".""id""")

I realise this is a limitation of the gem, It would be nice if it could support this as currently it's not usable with any queries that order by a joins association.

I have added basic support for it in our fork. https://github.com/residently/active_record_distinct_on/commit/f8c6dc5472000bb47ce7a030853f6c7ccba4bdb5

alecdotninja commented 2 years ago

I don't think accepting a hash for distinct_on is a good idea. It would make it inconsistent with other methods that accept columns (like order and select).

I have not personally tested this, but I think you should be able to write something like:

Article.joins(:author).includes(:author).merge(Author.distinct_on(:id))

This strategy also works for order and select.

raldred commented 2 years ago

Fair point. I guess i figured given it's related to associations, i could follow a similar interface tojoins, includes,references etc, which do take a variety of arguments.

I completely overlooked using merge here. I'll give that a whirl.

I do think if you want to remain consistent with select and order, distinct_on could take strings for associations eg. authors.id. particularly if you want to select distinct on an attribute on your primary resource as well as an associations attribute.

alecdotninja commented 2 years ago

I do think if you want to remain consistent with select and order, distinct_on could take strings for associations

I agree! I am surprised that does not work. It seems like a bug to me.

raldred commented 2 years ago

Yeh it doesnt because the code only allows attributes on the current arel_table.

I've taken a crude approach to it here. https://github.com/residently/active_record_distinct_on/commit/3fba30890a1995870f1391253706bade208d3466

This satisfies my requirement to use distinct on with a joined association.