Rails use SQL group_by

Question

I got a model call Answer, and here is its table

id      group_id    qset_id 
1       123            1
2       123            2
3       123            1
4       456            1
5       456            1
6       456            3

I need to return a array or json format include every group_id and their different qset_id

Maybe like this

class: [
    {
        "group_id": 123
        "qset_id": [1,2]
    },
    {
        "group_id": 456
        "qset_id": [1,3]
    }
]

I thought about group_by, but I am stuck here. And the table is very large, is there a efficient Rails way to handle that?

Breno Perucchi · Accepted Answer · 2016-08-10 07:39:05Z

2

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.

[email protected] (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"}

edited Aug 10, 2016 at 7:39

answered Aug 10, 2016 at 2:35

Breno Perucchi

8937 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rj487 Over a year ago

I am not sure, cause it took to long and the result didn't show. The Answer is very huge (more than 10 million record)

Breno Perucchi Over a year ago

So you need to do this action directly in sql

Collectives™ on Stack Overflow

Rails use SQL group_by

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related