from子句中的横向表

Question

我有3个表用户、角色和角色用户，其中最后一个表是中间表。所以，用户可以有多个角色，任何角色都可以属于多个用户。问题陈述是，我们需要呈现与搜索条件匹配的用户及其角色的列表。

select users.id, dynamic_roles.name
from users, lateral ( 
select GROUP_CONCAT( DISTINCT( roles.name ) ) as name
from roles, roles_users 
where (
(  roles_users.user_id = users.id AND roles_users.role_id = roles.id )
) 
) dynamic_roles
where dynamic_roles.name LIKE '%admin%' AND dynamic_roles.name LIKE '%manager%';

我尝试的如下：

rs = Role.joins(:users).select("GROUP_CONCAT( DISTINCT( #{Role.table_name}.name ) ) as name")
users = User.arel_table #predefined reference received as argument to a method that is supposed to compose the arel query.
users = users.project(users['id']).distinct
users.to_sql
=> "SELECT DISTINCT users.id FROM users"
users.from('dynamic_roles').to_sql
=> "SELECT DISTINCT users.id FROM dynamic_roles"
users.lateral('dynamic_roles').to_sql
=> TypeError: Cannot visit Arel::Nodes::Lateral
from /Users/prasadsurase/.rvm/gems/ruby-2.7.1/gems/activerecord-6.0.3.6/lib/arel/visitors/visitor.rb:39:in `rescue in visit'
Caused by NoMethodError: undefined method `visit_Arel_Nodes_Lateral' for #<Arel::Visitors::MySQL:0x00007f97424477b8>

参考https://apidock.com/rails/v6.0.0/Arel/SelectManager/from和https://apidock.com/rails/v6.0.0/Arel/SelectManager/lateral

Answer 1

您的问题是mysql访问者不包括Lateral的访问者(就像postgres那样(。

我们可以添加自己的访问者，但使用NamedFunction更容易解决这个问题，因为LATERAL语法与函数相同。

与大多数复杂的事情一样，这不是最漂亮的解决方案，但它会提供所需的结果。

roles = Arel::Table.new('roles')
role_users = Arel::Table.new('role_users')
users = Arel::Table.new('users')
group = Arel::Nodes::NamedFunction.new('GROUP_CONCAT',
[Arel::Nodes::Grouping.new(roles[:name])],
'name')
group.distinct = true
sub =  roles.project(group)
.from([roles, role_users])
.where( role_users[:user_id].eq(users[:id]).and( 
role_users[:role_id].eq( roles[:id]))
)
dynamic_roles = Arel::Table.new('dynamic_roles')
lateral = Arel::Nodes::NamedFunction.new('LATERAL',[sub], dynamic_roles.name)
users.project(users[:id], dynamic_roles[:name])
.from([ users, lateral])
.where( dynamic_roles[:name].matches(Arel::Nodes::Quoted.new("%admin%")).and(
dynamic_roles[:name].matches(Arel::Nodes::Quoted.new("%manager%"))
))

这将生成以下SQL:

SELECT users.id, dynamic_roles.name 
FROM users, LATERAL(
(
SELECT GROUP_CONCAT( DISTINCT (roles.name)) AS name 
FROM roles, role_users
WHERE role_users.user_id = users.id AND role_users.role_id = roles.id
)
) AS dynamic_roles 
WHERE 
dynamic_roles.name LIKE '%admin%' AND dynamic_roles.name LIKE '%manager%'