rails active record - Advanced find

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_belo开发者_运维百科ngs_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?

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.

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.

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

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")