Блог

Product В моем приложении Rails (версия 6.0) у меня есть name модель, в которой перечислены и цвет (на самом flavors['color'] деле flavors , потому что варианты продуктов в виде цвета хранятся в именованном поле jsonb Order ) каждого продукта, и связанная модель, которая имеет и принадлежит многим продуктам (HABTM) (на самом деле это отношение HMTс таблицей соединений, в которой указано количество продуктов на заказ, но давайте пока проигнорируем это).

Я могу подсчитать количество заказанных продуктов, сгруппированных по цвету продукта, с помощью этого запроса

 Order.joins(:products).group(:name,"products.flavors ->> 'color'").order(:name,'products_flavors_color').count
 

который выводит

 => {["jacket", "black"]=>59, ["jacket", "orange"]=>34, ["jacket", "white"]=>9, ["jacket", "red"]=>1, ["sockets", "black"]=>76, ["sockets", "green"]=>6, ["gloves", "black"]=>94, ["gloves", "green"]=>9, ["shirt", "black"]=>120, ["shirt", "orange"]=>62, ["shirt", "white"]=>19, ["shirt", "red"]=>3, ["pants", "black"]=>129, ["pants", "orange"]=>63, ["pants", "white"]=>18, ["pants", "red"]=>3, ["hat", "black"]=>86, ["hat", "orange"]=>45, ["hat", "white"]=>13, ["hat", "red"]=>1}
 

Я хотел бы иметь возможность агрегировать (с помощью массива или хэша jsonb) количество заказанных продуктов по цвету каждого продукта, чтобы иметь такой объект:

 {"jacket"=>{"black":59,"orange":34,"white":9,"red":1},"sockets"=>{"black":76,"green":6},"gloves"=>{"black":94,"green":9},"shirt"=>{"black":10,"orange":62,"white":19,"red":3},"pants"=>{"black":129,"orange":63,"white":18,"red":3},"hat"=>{"black":86,"orange":45,"white":13,"red":1}}
 

Вместо того, чтобы манипулировать объектом в Rails (если это не может быть сделано с помощью очень эффективного метода), я бы предпочел, чтобы это выполнялось СУБД (PostgreSQL 12.4) с использованием агрегатной функции, такой как array_agg() или jsonb_agg() .

Я пробовал что-то вроде этого

 Order.joins(:products).select(:name,"ARRAY_AGG(products.flavors ->> 'color')").group(:name).count(:name)
 

но он теряет агрегатную функцию. Поскольку это больше похоже на вопрос PostgreSQL, также принимаются подсказки so pure SQL, но я хотел бы иметь возможность реализовать запрос с помощью независимых методов запроса активных записей (кроме агрегатной функции, но избегая find_by_sql или похожих на arel).

Вы не сможете изначально получить нужный вам результирующий объект из SQL-запроса просто потому, что вы не можете вложить группировку таким образом; однако, что вы можете сделать, это преобразовать результат Hash в Hash нужную вам структуру.

Например

 result = Order.joins(:products)
              .group(:name,"products.flavors ->> 'color'")
              .order(:name,'products_flavors_color')
              .count
#=> {["jacket", "black"]=>59, ["jacket", "orange"]=>34, ["jacket", "white"]=>9, ["jacket", "red"]=>1, ["sockets", "black"]=>76, ["sockets", "green"]=>6, ["gloves", "black"]=>94, ["gloves", "green"]=>9, ["shirt", "black"]=>120, ["shirt", "orange"]=>62, ["shirt", "white"]=>19, ["shirt", "red"]=>3, ["pants", "black"]=>129, ["pants", "orange"]=>63, ["pants", "white"]=>18, ["pants", "red"]=>3, ["hat", "black"]=>86, ["hat", "orange"]=>45, ["hat", "white"]=>13, ["hat", "red"]=>1}

desired_result = result.each_with_object(Hash.new {|h,k| h[k] = {}}) do |((article,color),count),obj|
                    obj[article][color] = count
                 end
#=> {"jacket"=>{"black"=>59, "orange"=>34, "white"=>9, "red"=>1}, 
# "sockets"=>{"black"=>76, "green"=>6}, 
# "gloves"=>{"black"=>94, "green"=>9}, 
# "shirt"=>{"black"=>120, "orange"=>62, "white"=>19, "red"=>3}, 
# "pants"=>{"black"=>129, "orange"=>63, "white"=>18, "red"=>3}, 
# "hat"=>{"black"=>86, "orange"=>45, "white"=>13, "red"=>1}}