リレーションしたidのカウント

Question

現在売り上げ集計システムを作成しており、オーダーテーブルにリレーションしている商品idをどうLaravelのSQLでカウントすればいいのか分からず詰まっています。

通常のSQLだと中間テーブルに入っている商品IDをこのSQL（SELECT COUNT(product_id = ? or null) FROM sale_product）でカウントすればどの商品IDがいくつ売れたのかカウントできると思います。

laraval上でも一旦商品IDを取得後foreachで回して下記のようにコードを書いて実行したのですが
カウントされるのですが、繰り返しされず値が一つのみ返されます。

foreach ($products as $product){
       $counts = DB::select('SELECT COUNT(product_id = ? or null) FROM sale_product',[$product->id]);
}

どのように書けば最適にカウントされるでしょうか。
ごお教授いただけると嬉しいです！

Comments

[cerfweb]

お尋ねになっていることには直接関係ないと思いますが、SQL文を書くときは以下のようにテーブル名及びフィールド名をバッククォートで括ることをお勧めします。
'SELECT COUNT(`product_id` = ? or null) FROM `sale_product`'
特にフィールド名はバッククォートで括っていないとサーバによってはエラーを返すこともありますし、こうした方が処理も若干速くなるようです。

Answer 1

リレーション関連の情報を書かないと分からない。

countとか

$count = $products->sale_product()->count();

withCountとかLaravelならもっと簡単な方法があるのでは。
https://readouble.com/laravel/5.7/ja/eloquent-relationships.html#counting-related-models

foreachで回すなんてことはない。

Answer 2

流れが分かりませんが、提示のコードだと

$counts = DB::select('SELECT COUNT(product_id = ? or null) FROM sale_product',[$product->id]);

$countsという単一の変数を常に上書きしていっている状態なので、最後の情報しか残ってません。
配列に入れるとか別途工夫が必要に思います。

例：

$counts = [];
foreach ($products as $product){
       $counts[] = DB::select('SELECT COUNT(product_id = ? or null) FROM sale_product',[$product->id]);
}

紐付けしたいなら下記のような感じで

$counts = [];
foreach ($products as $product){
       $counts[$product->id] = DB::select('SELECT COUNT(product_id = ? or null) FROM sale_product',[$product->id]);
}