CODEWITHSUNDEEP
mysqlruby-on-railsrails-activerecord
AlexLarra
AlexLarra

Reputation: 881

Use GROUP_CONCAT query in Rails

I need the ids of a table but #pluck is not fast enough because there are too many records. The thing is that I would like to get them in a string directly from mysql instead of get any Array or ActiveRecord::Relation

[1, 2, 3] => "1,2,3"

There is no group_concat in Rails, so I just asked via sql. Example:

sql = User.select("GROUP_CONCAT(users.id)").to_sql
ActiveRecord::Base.connection.exec_query(sql)

The thing is that I don't know why but it does not return all the ids of the table, but just some of them.

Any idea why is not returning all of them or how can I achieve it in a different way?

Upvotes: 4

Views: 758

Answers (2)

AlvaroFdez
AlvaroFdez

Reputation: 66

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

Upvotes: 5

Eyeslandic
Eyeslandic

Reputation: 14890

You could just call it like this

sql = "select GROUP_CONCAT(id) from users"
data = ActiveRecord::Base.connection.exec_query(sql)
# #<ActiveRecord::Result:0x0000560661a2b7d8 @columns=["ids"], @rows=[["41,40,38,42,39,43,45,44"]], @hash_rows=nil, @column_types={}>

then you can get the data as ids with

ids = data['ids'].split(',').map(&:to_i)
# [41, 40, 38, 42, 39, 43, 45, 44]

Upvotes: 0

Related Questions