How to list records of a Table with a counter of how many are used through a relationship

Question

I have these 2 tables:

create_table "instruments", force: :cascade do |t|
    t.string   "name"
    ...

  create_table "user_instruments", force: :cascade do |t|
    t.integer  "user_id"
    t.integer  "instrument_id"
    ...

With these relationships:

class Instrument < ActiveRecord::Base

  has_many :user_instruments
  has_many :users, :through => :user_instruments

class UserInstrument < ActiveRecord::Base

  belongs_to :user
  belongs_to :instrument

How to query all the instruments, with a counter of how many times it is being used, like:

Instrument.select('name,User_instrument.group(:instrument_id).count').all

or maybe:

UserInstrument.includes(:instruments).group('instrument_id as count').select('instrument.name, count')

This works at the view level but it creates too many queries:

<% @instruments.each_with_index do |instrument,idx| %>
      ...
    <td><%= instrument.users.count %></td>
      ...
<%end%>

How to do that with 1 query?

Thanks

Answer 1

If you want to just show count then you can use to_a method to convert it to array and then call count method on it.

# Controller
@instruments = Instrument.all.eager_load(:users)

# View
<% @instruments.each do |instrument| %>
  <td><%= instrument.users.to_a.count %></td>
<% end %>