如何在Rails中使用GROUP_CONCAT?

问题描述：

我有以下查询，希望与ActiveRecord一起使用，以便可以在生产服务器上基于本机ORACLE的查询中翻译该查询.现在我正在使用SQLITe.

I have the following query which I want to use with ActiveRecord so that it can be translated in native ORACLE based query on production server. Right now I am using SQLITe.

select c.name,co.code,GROUP_CONCAT(c.name) AS GroupedName
from countries c
INNER JOIN continents co
on c.continent_code = co.code
INNER JOIN event_locations el
on el.location_id = c.id
group by co.code

答

据我所知，Rails中没有group_concat等效项，但是您可以使用includes来做到这一点:

As long as I know, there's no group_concat equivalent in Rails, but you can use includes to do that:

continents = Continents
  .joins(:countries, :event_locations)
  .includes(:countries)
  .group("continents.code")

continents.each do |continent| 
  continent.countries.join(",")
end

这只会产生2个查询-我知道，这不如一个查询好，但是我认为这是Rails在没有"group_concat"的情况下所能做到的最好的查询.另一种方法是这样的:

This will produce only 2 queries - I know, is not so good as one, but I think that is the best than Rails can do without "group_concat". The other way will be something like that:

Country
  .select("countries.id, GROUP_CONCAT(countries.name)as grouped_name")
  .joins(:continents, :event_locations)
  .group("continents.code")

但是，如果这样做，则需要根据数据库供应商进行更改.

But if you do that, you need to change according to your database vendor.

MySQL :group_concat(countries.name)
PostgreSQL : string_agg(countries.name，'，')
Oracle :listagg(countries.name，'，')

MySQL: group_concat(countries.name)
PostgreSQL: string_agg(countries.name, ',')
Oracle: listagg(countries.name, ',')

如何在Rails中使用GROUP_CONCAT?

相关推荐