ruby 在子查询中使用父值[已解决]

tf7tbtn2  于 2023-06-22  发布在  Ruby
关注(0)|答案(1)|浏览(123)

我有个问题如何在子查询选择中使用父属性?
示例

Record A
id: 1
parent_id: 1
name: Test A

Record B
id: 2
parent_id: 1
name: Test B

Record c
id: 3
parent_id: 2
name: Test c

查询结果:[{name:'Test A',total:2},{name:'Test B',total:2},{name:'Test C',total:1}]
我有这个疑问

Record.select('(select COUNT(*) from records where records.parent_id = parent_id), name')

但这会得到所有记录的总数,只有在我设置id时才有效,例如

Record.select('(select COUNT(*) from records where records.parent_id = 2), name')
hgncfbus

hgncfbus1#

直接在查询本身中编写所需的格式可能很复杂。相反,您可以使用查询检索计数,并使用Ruby代码格式化结果。

records = Record.group(:parent_id).select("group_concat(name) as names, count(*) as total")
res = []
records.each do |record|
  name_arr = record['names'].split(",")
  res << name_arr.map{|name| {'name' => name, 'total' => record['total']}}
end
return res

相关问题