CODEWITHSUNDEEP
ruby-on-railspostgresql
Rockster160
Rockster160

Reputation: 1658

Count associations based on attribute value

Given a class, I would like to return all objects (index) sorted by the number of associations they have with a specific attribute.

For example, let's say User has_many :fish I want to show a list of all users and order them by how many :fish they have that are color: :red (Assuming that's an enum/integer value)

Users with 0 red fish, or 0 fish at all, should still be shown on the table, but sorted to the bottom.

-- User -- | - # of fish - | - # of red fish -
    2      |      10       |        10
    5      |      25       |         7
    6      |      11       |         6
    7      |      18       |         5
    1      |      27       |         4
    3      |      23       |         1
    4      |       3       |         0
    8      |       0       |         0

So far I've managed to sort the User by the number of fish they have using the left_join gem, but I'm having trouble sorting by an attribute on that model.

This is how I am sorting by the number of fish:

@users = User.left_join(:fish).group("user.id").order("count(user.id) desc")

Any help would be awesome! I'm assuming this is trivial, but I'm also displaying that data as well, so any way to store that data temporarily would be awesome too! (I'm assuming using a select and setting the counts as variables)

EDIT:

Solution, thanks to Anthony E.

@users = User.select("users.*")
             .joins("LEFT JOIN fishes ON fishes.user_id = users.id")
             .where(fishes: { color: Fish.colors[:red] } )
             .group("users.id").order("COUNT(fishes.id) DESC")
             .page(params[:page]) // To work with Kaminari/pagination

Upvotes: 1

Views: 56

Answers (1)

Anthony E
Anthony E

Reputation: 11245

How about:

User.select("user.*, COUNT(fish.id) AS fish_count")
      .joins(:fish)
      .where(fish: { color: "red" })
      .group("user.id")
      .order("fish_count DESC")

The benefit is that you can call fish_count on each object in the returned returned collection because it's in your select

EDIT

To include users with 0 fish, use a LEFT JOIN and modify the where clause:

User.select("user.*, COUNT(fish.id) AS fish_count")
      .joins("LEFT JOIN ON fish.user_id = user.id")
      .where("fish.color = 'red' OR fish.color IS NULL")
      .group("user.id")
      .order("fish_count DESC")

Upvotes: 1

Related Questions