How to get distinct polymorphic association

user_9852976 0
Viewed 736

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.

postgresql ruby-on-rails ruby sql database
3 Answers

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)
user_24833130

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.

user_62453370

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)
user_37385420
Related