rails active record - Advanced find

Question

I have the following model setup - a User is interested in projects in many project Categories. Each Project has many categories. Like so:

class User
  has_many :projects
  has_and_belongs_to_many :project_categories

class Project
  belongs_to :user
  has_and_belongs_to_many :project_categories

class ProjectCategory
  has_and_belongs_to_many :projects
  has_and_belongs_to_many :users

Now, I'd like to do a find for projects with any of the catogories that a certain user are interested in, i.e. if a user is interested in project categories A,B,C then I'd like to find projects which are part of one or more of those project categories.

Anyone?

Answer 1

I believe this is another instance where the ActiveRecord abstraction leaks. You'll probably need some SQL to do this.

Answer 2

First I would look up all the project ids for a given user id using a JOIN.

# This will give me the list of project ids for the categories that a user is interested in.
project_ids = Project.find("SELECT project_id FROM projects_project_categories JOIN project_categories_users ON project_categories_users.project_category_id = projects_project_categories.project_category_id WHERE project_categories_users.user_id = ?", user_id)

# Now that I have the list of ids I can do a simple primary key lookup. Each project object returned only has the project_id attribute populated since we only asked for the project_id above.
projects = Project.find(project_ids.collect(&:project_id))

I think that this is the least number of queries that you can do this in.

The above assumes that your join tables are called projects_project_categories and project_categories_users. Also note that I didn't actually test this.

Answer 3

You don't need SQL to do this, you can use AR's bult-in :include function.

Here we include the associated :user and :project_category, passing the specific user_id and an array of categories that we are interested in.

project = Project.find(:include => [:user, :project_categories], :conditions => {:user_id => user_id, :project_category_id => [A,B,C,D]})

You will need to tune the column names to your requirements, but you should be able to start with something like this.

Answer 4

Thanks for the help! Being a much too eager noob at the moment I haven't gone through all possible AR solutions, but it would be interesting if something like this brakes the AR abstractions as Pierre said. I went with a modified version of Randy's suggestion:

pids = Project.find_by_sql ["SELECT project_id FROM project_categories_projects JOIN project_categories_users ON project_categories_projects.project_category_id = project_categories_users.project_category_id WHERE user_id = ?", current_user.id]
mytags_projects = Project.find(pids.collect(&:project_id), :limit => 10, :order => "created_at DESC")