How to Use Group_Concat in Rails

How can I use GROUP_CONCAT in Rails?

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, ',')

Use GROUP_CONCAT query in Rails

Apparently the result is truncated to the maximum length that is given by the group_concat_max_len.

Maybe you could increase that value. Follow this answer to get more information:

https://stackoverflow.com/a/5545904/8195530

group_concat does not show all the values mysql

I got the solution

SET SESSION group_concat_max_len = 1000000;

Run this code in MySQL console, then this code will change default group_concat character limit to 1000000 characters.

If you want to use in rails console,you can use in this following way

sql = "SET SESSION group_concat_max_len = 1000000"
ActiveRecord::Base.connection.execute(sql)

Please note:
This configuration will work only in that session

Rails use SQL group_by

Have you tried this ?

@answer = Answer.all.group_by(&:group_id) 

@answer.each_with_index do |answer, index|
  {
   'group_id': index,
   'qset_id: answer.flatten.map{|x| x_id}
  }    
end

---

I have to tell that I had to study more query sql to find this solution

ActiveRecord::Base.connection.exec_query('SELECT group_id, GROUP_CONCAT(qset_id) FROM answers GROUP BY group_id').rows.to_h

I created a test here for this and I received the correct answer.

4.1.15@2.2.0 (main)> ActiveRecord::Base.connection.exec_query('SELECT proposal_id, GROUP_CONCAT(DISTINCT unit_id) FROM bookings GROUP BY proposal_id').rows.to_h
  SQL (0.2ms)  SELECT proposal_id, GROUP_CONCAT(DISTINCT unit_id) FROM bookings GROUP BY proposal_id
=> {1=>"2,3", 3=>"4", 4=>"5", 5=>"6"}

How to get a list (of IDs) of grouped records using Rails+MySQL?

One way would be using GROUP_CONCAT which, to quote the docs, "returns a string result with the concatenated non-NULL values from a group." It would go something like this:

@cars = Car.group(:manufacturer)
          .select("id, manufacturer, COUNT(id) AS total_count, GROUP_CONCAT(id) AS car_ids")

@cars.first.car_ids # => "1,2,5,9"
@cars.first.car_ids.split(',') # => [ "1", "2", "5", "9" ]

I should caution, though, that having manufacturer as a text attribute of Car is going to become a headache pretty quickly. You'd be better off with a Manufacturer model that has_many :cars. This will allow you to do much smarter queries, e.g. Manufacturer.includes(:cars).all. That's why we use relational databases in the first place.

How to use Arel::Nodes::NamedFunction to define GROUP_CONCAT with SEPARATOR option

you nearly had it. All of the arguments go into the array (second argument). AFAIK, Arel has always been considered a private API, so documentation is pretty sparse.

(https://github.com/rails/rails/blob/354f1c28e81d9846fb9e5346fcca50cf303c12c1/activerecord/lib/arel/nodes/named_function.rb)

Arel::Nodes::NamedFunction.new('GROUP_CONCAT', [ta[:name], Arel.sql("SEPARATOR '|'")]).to_sql

Grouping by into a list with activerecord in rails

Roles.where(id: 2) already returns the single record. You might instead start with users and join roles table doing something like this.

User.
  joins(user_roles: :roles).
  where('roles.id = 2').
  select("user_roles.role, GROUP_CONCAT(DISTINCT roles.group_id SEPARATOR ',') ").
  group(:role)

Or, if you have the model for user_roles, start with it since you nevertheless do not query anything from users.

---

Related Topics