How to use `group_concat`

[nimmolo]

I found the syntax for group_concat(sep = nil, *orders, group: nil, order: nil) in /lib/arel_extensions/string_functions.rb. But I'm confused about how to call it...?

Note: I'm on Rails 5.2.7 with Arel 9.0.0, and the app is currently using a MySQL db, so it may not be available in my version. I notice that the method is defined in/required from /lib/arel_extensions/nodes/concat.rb and there's a comment in string_functions.rb # if Arel::VERSION.to_i < 7 so it may be getting skipped...

I'm getting NoMethodError Undefined method 'group_concat' for #<ActiveRecord::Relation []>

  def arel_select_translation_strings(user)
    t = Arel::Table.new(:translation_strings)
    v = Arel::Table.new(:translation_strings_versions)
    TranslationString.joins(t.join(v).on(t[:language_id].eq(id).
                            and(v[:translation_string_id].eq(t[:id])).
                            and(v[:user_id].eq(user.id))).join_sources).
      group(t[:id]).
      group_concat("\n", group: v[:text], order: :asc))
  end

If I try the last line:

select(group_concat("\n", group: v[:text], order: :asc))

I get NoMethodError undefined method 'group_concat' for main:Object. This error equally appears if I try (in rails console --sandbox)

group_concat("\n", group: TranslationString[:text], order: :asc).to_sql

so I feel the problem is somehow that the method is simply not defined, at least in my Rails instance. It is also possible that I need to add_sql_functions as stated in the README Usage section - I don't understand where to put this line, possibly in application.rb or environment.rb(?).

Reference: The SQL statement I'm trying to reproduce is this:

SELECT GROUP_CONCAT(CONCAT(v.text, "\n")) AS x
FROM translation_strings t, translation_strings_versions v
WHERE t.language_id = #{id}
  AND v.translation_string_id = t.id
  AND v.user_id = #{user.id}
GROUP BY t.id

[jdelporte]

Hi, You might want to write something like that:

 TranslationString.
   joins(t.join(v).on(t[:language_id].eq(id).
                            and(v[:translation_string_id].eq(t[:id])).
                            and(v[:user_id].eq(user.id))).join_sources).
   group(t[:id]).
   select(v[:text].group_concat("\n", order: [v[text].asc]).as('x'))

The kwarg group is here to use the group_concat as a window function.

As a clue to know on which object you can call ArelExtensions method you just need to remember that ArelExtensions propose more methods to extend Arel, not ActiveRecord, so it will always be on Arel object and never on ActiveRecord scopes.

The add_sql_functions methods should be execute in a rake deployment script

[nimmolo]

Thanks @jdelporte ! That works... these are so helpful. Do I understand that the kwarg order value should always be an array?

I realize also probably there's no need for me to alias the group_concat as x... I'm later plucking the values selected, but it seems if I pluck(v[:text]) from this select, it will be the array returned by the group_concat