CODEWITHSUNDEEP
sqlruby-on-railsactiverecordruby-on-rails-3.1associations
Msencenb
Msencenb

Reputation: 5104

Selecting records based on association count (join?)

Three models:

class Customer < ActiveRecord::Base
  has_many :visits
end

class Visit < ActiveRecord::Base
  belongs_to :customer
  has_many :messages
end

class Message < ActiveRecord::Base
  belong_to :visit
end

Now I want to return all of the customers visits in which they have messages. So in pseudo code something like this:

@customer = Customer.find(:id)
@customer.visits.where(visit has messages)

How do I do something like this?

Upvotes: 0

Views: 324

Answers (1)

clyfe
clyfe

Reputation: 23770

Do an inner join: (recommended):

# returns a customer's visits that have at least one message
@customer.visits.joins(:messages)

Make sure to deal with duplicates

@customer.visits.joins(:messages).(“distinct(categories.id, categories.name)”)

With possible performance issues, another option is to use SQL EXISTS clause:

@customer.visits.where("EXISTS (SELECT messages.id FROM messages WHERE messages.visit_id == visits.id)")

Or SQL IN:

@customer.visits.where("visits.id IN (SELECT messages.visit_id FROM messages)")

http://guides.rubyonrails.org/active_record_querying.html#using-array-hash-of-named-associations

Upvotes: 2

Related Questions