7

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
Volatil3
  • 14,253
  • 38
  • 134
  • 263
  • This is not a dup question at all. The OP wants to do the same using Rails. _voted_ to reopen it. – Arup Rakshit Jan 26 '15 at 08:47
  • 1
    @Ben It is not duplicate. I clearly mentioned Rails in it – Volatil3 Jan 26 '15 at 08:47
  • You want to have an Oracle query that does the same as the SQLite one? Or, you want someone to write you a load of RoR code that does the same thing? The first one is a duplicate; the second I agree wouldn't be. – Ben Jan 26 '15 at 08:53
  • I am talking about later. I know which methods provide similar functionality in SQLite and Oracle – Volatil3 Jan 26 '15 at 08:56

2 Answers2

19

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

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, ',')
sandre89
  • 5,218
  • 2
  • 43
  • 64
Daniel Loureiro
  • 4,595
  • 34
  • 48
0

On Rails 6 (I don't know in others), you can do generating only one query if you use select

continents = Continent.joins(:country).select(:name, 'countries.name as country_name')

countries_list = []

continents.each do |continent|
  countries_list << continent.country_name
end

I know the code is not grouping by the continent, but my idea was to show how to make the query at once and how to access the result. How to group you can do in the way it is better for you.

remember you need create the relationship in Continent class

has_many :country

hope I could help someone yet

danielbchaves
  • 113
  • 2
  • 9