How to get distinct polymorphic association

Question

I'm trying to get show a list of polymorphic relations without having any duplicates.

I have a StoreViews table with a polymorphic field called viewable (so there's a viewable_id and viewable_type column in my table). Now I want to display views with each polymorphic relation showing up just once, without duplicates.

@views = StoreView.
    .distinct(:viewable_id)
    .distinct(:viewable_type)
    .order("created_at DESC")
    .limit(10)

So if there's two records in StoreViews, both with the same viewable relation, @views should only return the most recent one. However, this is not the case.

How comes, that you linked the same viewable more than once to the StoreView? Usually you would avoid this using a foreign key constraint, however, that doesn't work for polymorphic associations, instead, you can validate the uniqueness of e.g. the viewable_id using a scope like that: `validates :viewable_id, uniqueness: { scope: :viewable_type }`, see: [rubyonrails.org#uniqueness](https://guides.rubyonrails.org/active_record_validations.html#uniqueness) — swiknaba, Dec 30 '18 at 14:20

score 6 · Answer 1 · answered Dec 27 '18 at 08:00

ORDER BY items must appear in the select list if SELECT DISTINCT is specified. There are several ways to work around this issue.

In this example using an aggregate function should work:

@views = StoreView
           .select('DISTINCT viewable_type, viewable_id, MAX(created_at)')
           .group(:viewable_type, :viewable_id)
           .order('MAX(created_at) DESC')
           .limit(10)

Ana María Martínez Gómez · Answer 2 · 2018-12-27T11:35:35.307

distinct only accept a boolean as a parameter to specify whether the records should be unique or not. So distinct(:viewable_id) is equivalent to distinct(true) and not doing what you want. Instead of using distinct, you should use group, which returns an array with distinct records based on the group attribute. To return the most recent one, apart from ordering (with order) by created_at, you need to add the fields in group:

@views = StoreView
         .order(viewable_id: :desc, viewable_type: :desc, created_at: :desc)
         .group(:viewable_id, :viewable_type)

If you need to have the returned records ordered by created_at, you would need to add this.

oren · Answer 3 · 2018-12-21T08:25:12.680

0

ActiveRecord distinct:

https://apidock.com/rails/ActiveRecord/QueryMethods/distinct

Specifies whether the records should be unique or not. For example:
User.select(:name)
# => Might return two records with the same name

User.select(:name).distinct
# => Returns 1 record per distinct name

How about this:

@views = StoreView
    .select(:viewable_id, :viewable_type)
    .distinct
    .order("created_at DESC")
    .limit(10)

You can also try

@views = StoreView
    .select('DISTINCT `viewable_id`, `viewable_type`')
    .order("created_at DESC")
    .limit(10)

edited Dec 21 '18 at 08:25

answered Dec 21 '18 at 08:07

oren

3,159
18
33

Unfortunately, neither of these worked. I was able to use `StoreView.select('DISTINCT ON (viewable_id, viewable_type) *').order("viewable_id, viewable_type, created_at DESC").limit(10)` and it only shows uniques, however it seems to disregard being ordered by *created_at* ? – NathanB Dec 21 '18 at 16:33
Not the most elegant solution, but you can always try to chain this at the end: `.reorder(nil).order(created_at: :desc)` to remove all added `ORDER BY` clauses and then add just one for created at. – swiknaba Dec 30 '18 at 14:24

How to get distinct polymorphic association

3 Answers3