Laravel5.8 サブクエリー(leftJoinSub)

説明

QueryBuilderがかなり使えるので、メモです。
サブクエリーもかんたんに書けます。

サブクエリーで合計をとります。

ユーザーへのメッセージがあって、それに対していいねをする想定のテーブル構成です。
以下、カラムは最小限で、適当です。
あと、正規化してない部分もあります。

テーブル

1.users ：ユーザーテーブル

論理名	物理名
id	PK
company_id	企業ID
name	名前

2.user_messages ：ユーザーメッセージテーブル

論理名	物理名
id	PK（メッセージのID）
user_id	ユーザーテーブルのID（貰った人）
message	メッセージ

3. user_message_likes ：ユーザーメッセージへのいいねテーブル

論理名	物理名
id	PK（いいねID）
company_id	企業ID
user_message_id	ユーザーメッセージテーブル のID

メッセージと、メッセージ毎のいいね数を取得する

<?php
// ユーザーメッセージIDでグループ化し、メッセージ毎のいいね数を取得するサブクエリー
$like_count = DB::table('users_message_likes')
->select('user_message_id', DB::raw('count(user_message_id) AS like_cnt'))
->groupBy('user_message_id');

// =====
// メッセージと、いいね数を取得する処理
// =====
$messages = DB::table('user_messages')
// ユーザー情報取得用のjoin
->join('users', 'users.id', '=', 'users_messages.user_id')
// いいね数取得用のサブクエリー(所属企業を絞る)
->leftJoinSub($like_count, 'count_table', function ($join) use ($company_id) {
	$join->on('users_messages.id', '=', 'count_table.user_message_id')->where('count_table.company_id', '=', $company_id);
})
->orderBy('users_messages.id', 'DESC')
->where(['users.company_id', '=',  $company_id])
->select([
	'users.name AS name',
	'user_messages.id AS id',
	'user_messages.message AS message',
	'count_table.like_cnt  AS like_cnt',
])
->get();

// レコードあるか
if (count($messages) == 0) {
	// 抜けるなどの処理
}

// レコード存在時は、ループして取得する
foreach ($messages as $message) {
	// データ操作
}

追伸

結局、カウントは集計をテーブルに持ったので使いませんでしたが、
覚えておいたら使えると思います。