Have query working but want ActiveRelation instead

Question

A project has legacy_users and group_users. group_users has to go through groups to get the users. I'd like to build a project.users that combines these queries as an arel.

Here is Project:

class Project < ActiveRecord::Base
  has_many    :project_accesses

  has_many    :groups,       through: :project_accesses, source: :group
  has_many    :legacy_users, through: :project_accesses, source: :user
  has_many    :group_users,  through: :groups, source: :users
end

Here is sql for Project.find(1).legacy_users:

SELECT "users".* FROM "users" INNER JOIN "project_accesses" ON "users"."id" =
"project_accesses"."user_id" WHERE "users"."deleted_at" IS NULL AND 
"project_accesses"."deleted_at" IS NULL AND "project_accesses"."project_id" = 1

Here is sql for Project.find(1).group_users:

SELECT "users".* FROM "users" INNER JOIN "group_memberships" ON "users"."id" =
"group_memberships"."user_id" INNER JOIN "groups" ON "group_memberships"."group_id" = 
"groups"."id" INNER JOIN "project_accesses" ON "groups"."id" = 
"project_accesses"."group_id" WHERE "users"."deleted_at" IS NULL AND 
"group_memberships"."deleted_at" IS NULL AND "groups"."deleted_at" IS NULL AND 
"project_accesses"."deleted_at" IS NULL AND "project_accesses"."project_id" = 1

By simply adding an OR on the INNER JOIN "project_accesses" condition, I get exactly what I want. Project.find(1).users:

SELECT "users".* FROM "users" INNER JOIN "group_memberships" ON "users"."id" = 
"group_memberships"."user_id" INNER JOIN "groups" ON "group_memberships"."group_id" = 
"groups"."id" INNER JOIN "project_accesses" ON ("groups"."id" = 
"project_accesses"."group_id" OR "users"."id" = "project_accesses"."user_id") WHERE 
"users"."deleted_at" IS NULL AND "group_memberships"."deleted_at" IS NULL AND 
"groups"."deleted_at" IS NULL AND "project_accesses"."deleted_at" IS NULL AND 
"project_accesses"."project_id" = 1

Basically I just changed to make the query do what I want:

INNER JOIN "project_accesses" ON ("groups"."id" = "project_accesses"."group_id" OR "users"."id" = "project_accesses"."user_id")

Problem is that right now, this is just a string passed into find_by_sql and I can't figure out how to make this a proper Arel.

Thanks in advance for help!

score 0 · Answer 1 · answered Sep 27 '13 at 03:32

0

You should be able to use the joins method of ARel to manually build your join and still get back a live relation.

http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-joins

answered Sep 27 '13 at 03:32

Jeremy Green

8,547
1
29
33

score 0 · Answer 2 · answered Sep 27 '13 at 18:39

I'm getting closer:

User.joins(:groups).joins("inner join project_accesses on (groups.id = 
project_accesses.group_id or users.id = project_accesses.user_id)").
where(project_accesses: {project_id:1}).merge(Group.scoped).
merge(GroupMembership.scoped).merge(ProjectAccess.scoped).uniq

Is there any way to write this part in Arel?

joins("inner join project_accesses on (groups.id = 
project_accesses.group_id or users.id = project_accesses.user_id)")

Also, is there a way to automatically include the default scopes for all of the joined models, to avoid:

.merge(Group.scoped).merge(GroupMembership.scoped).merge(ProjectAccess.scoped)

Have query working but want ActiveRelation instead

2 Answers2