Merge has_many and has_many through queries together

Question

This seems like a typical problem, but it is difficult to search for.

I want to select projects that a user owns via a has_many and projects that a user is associated to via a has_many through.

Consider the following models:

class User < ActiveRecord::Base
  has_many :projects,
           inverse_of: :owner

  has_many :project_associations,
           class_name: 'ProjectUser',
           inverse_of: :user

  has_many :associated_projects,
           through: :project_associations,
           source: :project
end

class Project < ActiveRecord::Base
  belongs_to :owner,
             class_name: 'User',
             foreign_key: :owner_id,
             inverse_of: :projects

  has_many :user_associations,
           class_name: 'ProjectUser',
           inverse_of: :project

  has_many :associated_users,
           through: :user_associations,
           source: :user
end

class ProjectUser < ActiveRecord::Base
  belongs_to :project,
             inverse_of: :user_associations

  belongs_to :user,
             inverse_of: :project_associations
end

It is trivial to do this with multiple queries:

user = User.find(1)
all_projects = user.projects + user.associated_projects

But I suspect it could be optimised using Arel into a single query.

Edit:

My first attempt at a solution using the find_by_sql method is:

Project.find_by_sql([
  'SELECT "projects".* FROM "projects" ' \
  'INNER JOIN "project_users" ' \
  'ON "projects"."id" = "project_users"."project_id" ' \
  'WHERE "project_users"."user_id" = :user_id ' \
  'OR "projects"."owner_id" = :user_id',
  { user_id: 1 }
])

This produces the result I am expecting, but I would like to avoid using find_by_sql and instead let Arel build the SQL.

score 2 · Answer 1 · edited May 23 '17 at 12:15

This should work:

Project.joins(:user_associations)
       .where("projects.owner_id = ? OR project_users.user_id = ?", id, id)

You could put that in a method called User#all_projects.

You could also use UNION, although it seems like overkill here.

Just one warning: In my experience this structure of two alternate paths to connect your models, where one path is a join table and the other is a direct foreign key, causes a lot of trouble. You're going to have to deal with both cases all the time, and in complex queries the OR can confuse the database query planner. If you're able to, you might want to reconsider. I think you will have fewer headaches if you remove the owner_id column and identify owners with a role column on project_users instead.

EDIT or in Arel:

Project.joins(:user_associations)
       .where(Project.arel_table[:owner_id].eq(id).or(
              ProjectUser.arel_table[:user_id].eq(id)))

Thanks Paul, this will work, but for the solution I am looking for a way to use Arel without touching sql directly. I understand the double association can cause problems, but at this point refactoring is not an option (at least for a while). — Marc Greenstock, Mar 21 '16 at 16:42

score 1 · Answer 2 · answered Mar 21 '16 at 09:54

1

You can use the includes directive as in:

User.includes(:projects, :associated_projects).find(1)

Check http://apidock.com/rails/ActiveRecord/QueryMethods/includes

Then if you call projects and associated_projects on the found instance you'll not fire additional queries.

answered Mar 21 '16 at 09:54

alediaferia

2,537
19
22

Not exactly what I'm looking for. This just pre-caches the result set of `projects` and `associated_projects`, and is done in multiple queries. I am looking for a single query that produces a single result set of `projects` that the user owns and is associated to. – Marc Greenstock Mar 21 '16 at 10:18
AFAIK `includes` uses `join` whenever possible, so it'd be one single query. – alediaferia Mar 21 '16 at 10:35
Unfortunately not. What will be returned from your example is a single `user` row. It performs a query on the `project_users` table to fetch the project ids (1st query), a query on the `projects` table with the `owner_id` (2nd query) and another query on the `projects` table where id in project ids from the 1st query. Total queries = 3. Regardless of the number of queries performed, the result is not what I am looking for. I need a AR `CollectionProxy` of `Projects` that are associated to the user, through `owner_id` and the association model. – Marc Greenstock Mar 21 '16 at 12:20

score 0 · Answer 3 · answered Mar 21 '16 at 16:32

I have my solution, the model structure remains the same, and I add a method on the User model called all_projects.

class User < ActiveRecord::Base
  has_many :projects,
           inverse_of: :owner

  has_many :project_associations,
           class_name: 'ProjectUser',
           inverse_of: :user

  has_many :associated_projects,
           through: :project_associations,
           source: :project

  def all_projects
    query = Project.arel_table[:owner_id].eq(id).or(ProjectUser.arel_table[:user_id].eq(id))
    Project.joins(:user_associations).where(query)
  end
end

Calling #all_projects builds and executes this query:

SELECT "projects".*
FROM "projects"
INNER JOIN "project_users" ON "project_users"."project_id" = "products"."id"
WHERE ("projects"."owner_id" = '1' OR "project_users"."user_id" = '1')

The solution isn't as elegant as I would like, ideally I would like to replace Project.arel_table[:owner_id].eq(id) with the query that is built from the has_many projects association, adding the .or(ProjectUser.arel_table[:user_id].eq(id)) onto it, but this works well for now.

Merge has_many and has_many through queries together

3 Answers3