[FeatureRequest] Support joined associations

[raldred]

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.
residently@f8c6dc5

[alecdotninja]

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]

Fair point. I guess i figured given it's related to associations, i could follow a similar interface to joins, 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]

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]

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

I've taken a crude approach to it here.
residently@3fba308

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

[ezekg]

I'm open to accepting strings for association support if anybody wants to provide a PR, e.g.:

Author.joins(:books).distinct_on('books.title')

I've needed this before quite a few times.