Ordering results with ActiveRecord and Postgres with has_many through statement

Question

I have these 3 models here:

class User < ActiveRecord::Base
  has_many :parties, foreign_key: 'organizer_id'

  has_many :invitations, foreign_key: 'attendee_id'
end

class Party < ActiveRecord::Base
  belongs_to :organizer, class_name: 'User'

    has_many :invitations
    has_many :attendees, through: :invitations
end

class Invitation < ActiveRecord::Base
  belongs_to :party
  belongs_to :attendee, class_name: 'User'
  belongs_to :inviter, class_name: 'User'
end

I want to get all attendees for a specific party and sort them by those who invited the most people.

How do I do this?

Thanks for your help.

BroiSatse · Accepted Answer · 2015-01-13 18:20:07Z

2

Couple of possible solution:

Dirty one

party.attendees.joins(:invitations).select('parties.*', 'COUNT(DISTINCT invitations.id) as invitation_count').group('parties.id').order('invitation_count')

Pros: might work

Cons: This is ugly, does not explain itself and you will end up with an extremely hard to query relation. Rather avoid.

Slightly cleaner

Sort it on the application level:

party.attendees.includes(:invitations).sort_by {|att| att.inivtaions.to_a.size }

Pros: It is more clear what you're doing

Cons: Returns non-querable array instead of relation. Still need explanation why you're calling to_a (to avoid N+1 problem).

The cleanest

Add an extra column invitations_count to attendees table, and add counter_cache: true to the belongs_to :attendee in your Invitation model. Profit!

party.attendees.order(:invitations_count)

Pros: Nice and self-explanatory. No need to load invitations from the database. Easily querable association.

Cons: You need to update all the records' counter_cache before you start using it:

Attendees.includes(:invitations).each { |att| att.invitation_count = att.invitation.to_a.size }

After this, rails will take care of holding the column in sync with a number of associated with attendee inivtations.

answered Jan 13, 2015 at 18:20

BroiSatse

44.8k8 gold badges63 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lpdahito Over a year ago

Great answer! Thx for explaining in details!

BroiSatse Over a year ago

Which solution did you tired?

Collectives™ on Stack Overflow

Ordering results with ActiveRecord and Postgres with has_many through statement

1 Answer 1

Dirty one

Slightly cleaner

The cleanest

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Dirty one

Slightly cleaner

The cleanest

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related