ruby-on-rails - 如何在 Rails 5 中编写两个内部连接

Question

您好，我想将此 sql 转换为 rails 事件记录

select a.*
from public.users a
inner join public.user_by_groups gp2 on gp2.user_id = a.id and gp2.active = 'true'
and gp2.group_id in 
(SELECT gp.group_id                     
  FROM public.users u
  inner join public.user_by_groups gp on gp.user_id = u.id and gp.active = 'true'
  where u.id = '4')

我的模型(多对多关系)

class User < ActiveRecord::Base
  has_many :user_by_groups
  has_many :groups, through: :user_by_groups
end

class UserByGroup < ApplicationRecord
  # Relations
  belongs_to :user
  belongs_to :group
end

class Group < ApplicationRecord
  has_many :user_by_groups
  has_many :users, through: :user_by_groups
end

还有第一个查询:

    @class_mates = User.joins(:user_by_groups)
.where(user_by_groups: { user_id: 4 })
.where(user_by_groups: { active: true })

Answer 1

试试这个:

inner_user = Arel::Table.new(:users, as: :i_user)
inner_ubg = Arel::Table.new(:user_by_groups, as: :i_ubg)

User.joins(:groups).where(
  Arel::Nodes::In.new(
    Group.arel_table[:id],
    inner_user.
      project(inner_ubg[:group_id]).
      join(inner_ubg).on(inner_user[:id].eq(inner_ubg[:user_id])).
      where(inner_ubg[:active].eq(true)).
      where(inner_user[:id].eq(4))
  )
).to_sql

#=> SELECT "users".* FROM "users"
#     INNER JOIN "user_by_groups" ON "user_by_groups"."user_id" = "users"."id"
#     INNER JOIN "groups" ON "groups"."id" = "user_by_groups"."group_id"           
#     WHERE "groups"."id" IN ((
#       SELECT "i_ubg"."group_id" FROM "users" "i_user"
#       INNER JOIN "user_by_groups" "i_ubg" ON "i_user"."id" = "i_ubg"."user_id"
#       WHERE "i_ubg"."active" = 't' AND "i_user"."id" = 4
#     ))