laravel5.4でのサブクエリ結果をjoinする方法につきまして

Question

###前提・実現したいこと
laravel5.4で、サブクエリした結果のテーブルをleft joinしたいのですが、どうしても上手くいかないです。
laravelでの正しい記述を教えて頂けないでしょうか？

恥ずかしながらPHP初心者で、ここからどうやって修正したらいいのかが調べてもサッパリ分からず手詰まっております。

###発生している問題・エラーメッセージ
２つエラーが出ていて
QueryException
PDOException

メッセージはともに

SQLSTATE[42000]: Syntax error or access violation:　〜

###該当のソースコード
こちらのSQLで取得できる結果を、

SELECT subjects.id, subjects.book_id, books.item_name, is_read_cnt.cnt FROM

subjects 
LEFT JOIN (  
select n.id as negotiate_id, count(m.id) as cnt from 
  subjects n
   LEFT JOIN  messages m
   ON n.id = m.negotiate_id
WHERE 
  n.buyer_user_id = ? 
AND
  m.is_read=0 
GROUP BY
  negotiate_id
  
) is_read_cnt

ON subjects.id = is_read_cnt.negotiate_id

LEFT JOIN books
on books.id = subjects.book_id

WHERE subjects.buyer_user_id = ?

こちらのlaravelで取得ができれば問題が解決します。

DB::table('negotiates')
                ->select(['negotiates.id','negotiates.book_id','books.item_name','is_read_cnt.cnt'])
                ->leftJoin("(  
select n.id as negotiate_id, count(m.id) as cnt from 
  negotiates n
   LEFT JOIN  messages m
   ON n.id = m.negotiate_id
WHERE 
  n.buyer_user_id = {$userid} 
AND
  m.is_read=0 
GROUP BY
  negotiate_id
  
) is_read_cnt","subjects.id","=","is_read_cnt.negotiate_id")
                ->leftJoin('books','books.id', '=', 'subjects.book_id')
                ->where('subjects.buyer_user_id',$userid)
                ->get()
                ->toArray();

素人質問で申し訳ありませんが、ご教示頂けますと幸いです。
よろしくお願いします。

Answer 1

下記のようにサブクエリを先に書いて、後でメインのクエリに繋げます。ベタでSQLを書いてもよいのですが、今回はサブクエリにもバインドしなければならない値があるようなので、DBを使ってオブジェクトにしています。

※注意点
サブクエリのWHERE句のバインド値をマージしないと（mergeBindings($sub)）サブクエリ内のWHERE句の値が消えてしまいます。

//サブクエリ
$sub = DB::table('subjects as n')
    ->select('n.id as negotiate_id', DB::raw('count(m.id) as cnt'))
    ->leftJoin('messages as m', 'n.id', '=', 'm.negotiate_id')
    ->where('n.buyer_user_id', 値)
    ->where('m.is_read', 0)
    ->groupBy('negotiate_id');

//メインクエリ
$query = DB::table('subjects')
    ->select('negotiates.id', 'negotiates.book_id', 'books.item_name', 'is_read_cnt.cnt')
    ->leftJoin(DB::raw('('. $sub->toSql() .') as is_read_cnt'), 'subjects.id', '=', 'is_read_cnt.negotiate_id')
    //サブクエリのWHERE句の値をバインド
    ->mergeBindings($sub)
    ->leftJoin('books', 'books.id', '=', 'subjects.book_id')
    ->where('subjects.buyer_user_id', 値);

//SQL文取得
//$query->toSql());

//WHERE句のバインド値を取得
//$query->getBindings();

dd($query->get());

Comments

[fujita222]

ご回答ありがとうございました！