php - custom subquery does not work and gives errors

Question

I'm working on an old php project and renewing that in Laravel 8. I'm trying to translate a pice of old sql query in Laravel query but I get an error and I don't know how to solve it. I hope you can help me with that.

Old code:

$row = $userdb->q("
            SELECT ci.id, ci.id_category, ci.subject, cit.name AS catname
            FROM contactinfo ci
            LEFT JOIN contactinfo_notes cin ON (cin.id=(SELECT id FROM contactinfo_notes cin2 WHERE cin2.id_ci=ci.id AND cin2.deleted=0 ORDER BY cin2.id DESC LIMIT 1))
            LEFT JOIN contactinfo_types cit ON (cit.id=ci.id_category);

New code:

 $collection = DB::table('contactinfo AS ci')
            ->select(
                'ci.id',
                'ci.id_category',
                'ci.subject',
                'cit.name AS catName'
            )
            ->leftJoin('contactinfo_notes AS cin', function ($query) {
                $query->select('cin2.id')
                    ->from('contactinfo_notes AS cin2')
                    ->where('cin2.id_ci', '=', 'ci.id')
                    ->where('cin2.deleted', 0)
                    ->orderBy('cin2.id', 'desc')
                    ->limit(1);
            }, '=', 'cin.id')
            ->leftJoin('contactinfo_types AS cit', 'ci.id_category', 'cit.id')
            ->get();

question from:https://stackoverflow.com/questions/65940544/custom-subquery-does-not-work-and-gives-errors

Answer 1

I convert your sql code adding DB Raw for the first leftjoin.

$collection = DB::table('contactinfo as ci')
        ->select('ci.id','ci.id_category','ci.subject','cit.name AS catname')
        ->leftJoin('contactinfo_notes cin', 'cin.id','=',DB::Raw('SELECT id FROM contactinfo_notes cin2 WHERE cin2.id_ci=ci.id AND cin2.deleted=0 ORDER BY cin2.id DESC LIMIT 1')
        ->leftJoin('contactinfo_types cit', 'cit.id','=','ci.id_category')->get();

Or you can remplace the first leftjoin with this code , witch is more readble.

->leftJoin('contactinfo_notes cin2', function($join)
    {
        $join->on('cin2.id_ci', '=', 'ci.id')
        ->where('cin2.deleted', 0)
        ->orderBy('cin2.id', 'DESC')
        ->take(1)->get();
    })