How to count a has_many relationship using active record?

user_10282224 0
Viewed 3376

I have a join table that lists a user_id and an item_id (many-to-many relationship).

I'm trying to show how many items a user owns on an erb page.

How do I go about doing this?

My join table/model UserItem has eleven records in right now, so obviously if I do UserItem.count, it gives me 11.

But how can I tell it that for every user_id, count how many item_ids there are for THAT particular user? And then obviously I would iterate over this.

EDIT:

Ok I found that UserItem.group(:user_id).distinct.count gives me back hash of {user_id => # of items per user}.

How do I not only iterate over the hash, but also make it so it associates it with each current user_id?

<% @users.each do |user| %>
          <li><%= user.username %>
          has been to <%= UserItem.group(:user_id).distinct.count %>!</li>
        <% end %>

As of now, this just inserts the hash, how do I correspond each user_id so it aligns with the user.username it's showing?

sinatra-activerecord activerecord ruby sql
1 Answers

I assume you are having an has_many :through association between User and Item through UserItem and you need to show the list in a view of all users with the related item count.

This is one option.

In controller (ordered by item_count):

@users = User.joins(:user_items).group('user_items.user_id').select('users.*, COUNT(*) as item_count').order('item_count DESC')

In view (very basic):

<% @users.each do |user| %>
  <p><%= user.name %> | <%= user.item_count %></p>
<% end %>

In my comment there is a syntax error, for counting the items of @user, if id = @user.id:

UserItem.where(user_id: id).count
**Edit1:** To show also users with no items.

Option one, add to the above code the following, to fetch users with no items:

@users_without_items = User.includes(:items).where(items: {id: nil})

<% @users_without_items.each do |user| %>
  <p><%= user.name %> | 0</p>
<% end %>

Or fetch all at once (not ordered, fires a lot of queries):

@user = User.all

<% @user.each do |user| %>
  <p><%= user %> | <%= user.categories.count %></p>
<% end %>
**Edit2:** Fetching a hash of `{user_id => # of items per user}`

One option can be list all user and get the count from the hash by it's keys:

@user_items_count = UserItem.group(:user_id).count
@users = User.all

<% @users.each do |user| %>
  <p><%= user.name %> | <%= @user_items_count[user.id] || 0 %></p>
<% end %>
**Tested in `Rails` which uses `ActiveRecord` and `erb`.**
user_52390300
Related