Filter on a Related Table

rubyonrails-talk
1

I have an application with two related tables as follows

   User       has_many :items

   Item       belongs_to :user

The description of the item table includes the following columns:    user_id int not null,    itemDate date

For a view I want to retrieve a list of users with each user appearing one time. For a given date, a user may or may not have any items. I want to retrieve the items for that user that correspond to a given date.

I find that doing this and retrieving *ALL* the items for a given user is quite easy. I don't see a way to filter it down to just the items that occurred on the given date. Do note: I need all the users whether or not they had any items on that date.

I can write logic to do this, but doing that pretty much steps outside the ActiveRecord relationships - or possibly bypasses them. Can anyone offer any suggestions?

Thanks in advance ---Michael

2

Michael Satterwhite wrote:

I have an application with two related tables as follows

   User       has_many :items

   Item       belongs_to :user

The description of the item table includes the following columns:    user_id int not null,    itemDate date

For a view I want to retrieve a list of users with each user appearing one time. For a given date, a user may or may not have any items. I want to retrieve the items for that user that correspond to a given date.

I find that doing this and retrieving *ALL* the items for a given user is quite easy. I don't see a way to filter it down to just the items that occurred on the given date. Do note: I need all the users whether or not they had any items on that date.

I can write logic to do this, but doing that pretty much steps outside the ActiveRecord relationships - or possibly bypasses them. Can anyone offer any suggestions?

Thanks in advance ---Michael   

This would be done best as separate queries. So, first get all your users and then for each look up the items for the specific date. I'd probably add an instance method to user to wrap this in, eg...

def items_for_date(date)     items.find(:all, :conditions => ['item_date = ?',date]) end

then as you loop through your users in the view, call user.items_for_date(@date) on each when you're ready for the items.