How to select distinct fields grouped by one field and concat them in one field with parameter in ruby


#1

I have an table that includes these field:

table phonebook

  id   
  user_id
  number
  name
  added
  card_id
  speeddial
  updated_at
  sms_group_name

some records have same sms_group_name and number, but there is some duplicate number for same sms_group_name. First I want to take distinct number for each sms_group_name and group_concat with , delimiter.

query result must be like this:

I want to take result as a <ActiveRecord::Relation [#<Phonebook user_id: 5, number: "xxxxx,xxxxxx,xxxxxx", sms_group_name: "tem1">, #<Phonebook user_id: 5, number: "xxxxx,xxxxxx,xxxxxx", sms_group_name: "tem2">

select where condition is user_id

I tried all of them :

@a = Phonebook.select(["DISTINCT number","sms_group_name"]).where(user_id: session[:user_id]).order(:sms_group_name).distinct
@a = Phonebook.where(user_id: session[:user_id])
Product.where.not(restaurant_id: nil).select("DISTINCT ON(name) name, restaurant_id, price, updated_at").order("name, updated_at")
@a = Phonebook.where(user_id: session[:user_id]).select("DISTINCT ON(number) number, added, user_id, speeddial, updated_at,sms_group_name").order("sms_group_name")
Phonebook.select("DISTINCT(number), *").where("user_id = ?", session[:user_id]).order("sms_group_name ASC").group_by(&:sms_group_name)
Location.where("calendar_account_id = ?", current_user.calendar_accounts.first).group(:alias).order("alias ASC").group_by(&:category)
@a = Phonebook.where("user_id = ?", session[:user_id]).order("sms_group_name ASC").group(:sms_group_name)
@a = Phonebook.select("DISTINCT(number), sms_group_name").where("user_id = ?", session[:user_id]).order("sms_group_name ASC").group_by(&:sms_group_name)
@a = Phonebook.select(:number)distinct.where("user_id = ?", session[:user_id]).order("sms_group_name ASC").group_by(&:sms_group_name)
@a = Phonebook.select("DISTINCT(number), sms_group_name").group("sms_group_name")
Phonebook.select("DISTINCT ON (number,sms_group_name) number,sms_group_name").where(user_id: session[:user_id]).order(:sms_group_name).group_by{ |p| p.sms_group_name }

most of them give error or does not work.

How can achieve this?

I tried select all values into object array, after that I tried to eliminate them, but there is no suitable solution for now.

what can be solutions for both:

  1. solution by using query and may be added one block
  2. solution by using hash or array

NOTE: I use mysql server

best regards,