select many through many... kind of

Question

Hi all!

This is my first post on Stack, so please bear with me if I breach any protocol.

I'm working on a project in Rails (2.1.2), and I have a relations scenario that looks like this:

event [has_many] days

People (in different categories) can sign up for event days, giving the following binding results:

category [has_many] customers [has_many] orders [has_many] days  
[belongs_to] event

Now, I'd like to have the total number of 'events' for one customer, or for all customers in a certain category, and I'm stuck. AFAIK, there's no way of performing a simple 'find' through an array of objects, correct? So, what would be the solution; nested loops, and a collect method to get the 'events' from the 'days' in orders?

Please let me know if I'm unclear.

Thanks for your help!

Answer 1

Your models probably look like this:

class Category
  has_many :customers

class Customer
  has_many :orders
  has_many :days, :through => :orders # I added this
  belongs_to :category

class Order
  has_many :days
  belongs_to :customer

class Day
  belongs_to :event
  belongs_to :order

class Event
  has_many :days

With this you can count events for customer:

events = customer.days.count(:group => 'event_id')

It will return OrderedHash like this:

#<OrderedHash {1=>5, 2=>13, 3=>0}>

You can get events count by:

events[event_id]

events[1] # => 5
events[2] # => 13
etc.

If you want total number of uniq events:

events.size # => 3

In case of counting events for all customers in category I'd do something like this:

events = {}
category.customers.each {|c| events.merge!(c.days.count(:group => 'event_id') ) }
events.size # => 9 - it should be total number of events

But in this case you lose information how many times each event appeared.

I didn't test this, so there could be some mistakes.

Answer 2

I would personally do this using a MySQL statement. I don't know for sure, but I think it is a lot faster then the other examples (using the rails provided association methods).

That means that in the Customer model you could do something like: (Note that I'm assuming you are using the default association keys: 'model_name_id')

class Customer
  def events
    Event.find_by_sql("SELECT DISTINCT e.* FROM events e, days d, orders o, customers c WHERE c.id=o.customer_id AND o.id=d.order_id AND e.id=d.event_id")
  end
end

That will return all the events associated with the user, and no duplicated (the 'DISTINCT' keyword makes sure of that). You will, as with the example above, lose information about what days exactly the user signed up for. If you need that information, please say so.

Also, I haven't included an example for your Category model, because I assumed you could adapt my example yourself. If not, just let me know.

EDIT:

I just read you just want to count the events. That can be done even faster (or at least, less memory intensive) using the count statement. To use that, just use the following function:

def event_count
  Event.count_by_sql(SELECT DISTINCT COUNT(e.*) FROM ... ... ...
end