やりたいこと
ある日付に発送する商品名一覧と、それぞれの発送数 ＝ 注文が決済された数を取得したい。
結論（完成形）
Product.where(delivery_date: delivery_date).
left_joins(:orders).
group(:id).
select('products.name, COUNT(orders.status = 1 or null) AS quantity')
今回の設定
テーブル
- 商品（products）
- 注文（orders）
主な登場人物
- ある日付： delivery_date
（productsの関連テーブル「delivery_date」のインスタンス。
今回ここは重要じゃないので、絞り込むための日付と捉えればOK） - 商品名： products.name
- 各商品が売れた数： orders.status = 1 （決済済）の件数
さて、どうやって実現しよう
まずはやりたい事を日本語で書く
日付で絞り込んだproductsテーブルとordersテーブルをLEFT OUTER JOIN（左外部結合）した上で
各productに紐づくordersのうち orders.status = 1 になってる数の合計を出す
（商品ごとの決済数合計を出す＝商品ごとにgroup化してcount）
やり方を考える
まずはLEFT OUTER JOIN（左外部結合）から。
みんな大好きeager_loadを使ってしまいそうになるが、
今回のように「結合先のテーブルでcountする」場合は使えないもよう。
結論としては、結合先のテーブルで COUNT をしたい場合は joins が有効です。
eager_load では、結合先テーブルの全カラムを select に入れてしまい、集計関数が使えなくなってしまうためです。
なので今回はleft_joinsを使うッ！
日付の絞り込みはもちろんwhereで。
Product.where(delivery_date: delivery_date).left_joins(:orders)
で、「商品ごとにgroup化してからcount」する必要があるので
product.idでグループ化させる。
Product.where(delivery_date: delivery_date).left_joins(:orders).group(:id)
そして、取得したいのは「商品名」と「決済された数量」。
Product.where(delivery_date: delivery_date).
left_joins(:orders).
group(:id).
select('products.name, COUNT(orders.status = 1) AS quantity')
これでできた！！沖田さん大勝利〜！！😍
・・・って思いきや、これだと意図した集計結果にならない。
理由：
COUNTは非NULL値の数を返すので、statusが1以外（0とか2とか）の場合も計上されてしまうため
COUNT(expr)
SELECT ステートメントで取得された行に含まれる expr の非 NULL 値の数を返します。結果は BIGINT 値になります。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.19.1 GROUP BY (集約) 関数
解決法
or nullをつけてあげる。
COUNT(orders.status = 1 or null)
これなら、orders.status = 1がtrueの場合は
true or null（trueとnullの和集合）= true すなわちカウント対象になり
falseの場合（orders.statusが0とか2の場合）は
false or null（falseとnullの和集合）= nullとされる＝非NULL値のためカウント対象にならない。
もっかい完成形（と出力例）
Product.where(delivery_date: delivery_date).
left_joins(:orders).
group(:id).
select('products.name, COUNT(orders.status = 1 or null) AS quantity')
こうすることで、
products.each do |product|
puts "商品名：#{product.name} ／ #{product.quantity}件"
end
# "商品名：うんまぁァァ〜いッ！！ただの水 ／ 204件"
# "商品名：アヴドゥルさんの養鶏所の卵 ／ 126件"
# "商品名：花京院がレロレロしたいさくらんぼ ／ 0件"
無事目的を達成できましたね！！😊
僥倖っ……！なんという僥倖……！