mercredi 22 juillet 2020

Problem trying to use elements from main query in subquery (Eloquent)

I'm trying to write a query using Laravel Eloquent methods, the original query looks like this:

SELECT distinct
    a.*, b.*
FROM
   a
INNER JOIN b ON
   a.id = b.id
WHERE
    a.id > 10
    AND NOT EXISTS(
        SELECT c.id
        FROM c
        WHERE 
            c.id = a.id
    )

What I was trying is looking like this right now:

\DB::table('a')
    ->join('b', 'a.id', '=', 'b.id')
    ->select('a.*', 'b.*')
    ->whereRaw('a.id > 10')
    ->whereNotIn('a.id', function($id) {
        $id = \DB::table('c')
            ->where(function($query) {
                $query->where('c.id', '=', 'a.id');
             })
             ->get();
        })
    ->distinct()->get()->first();

The problem I'm having is that I get an error converting type nvarchar to bigint cause in the line where I do '$query->where('c.id', '=', 'a.id');', I can't use the 'a.id' since I'm in a subquery and the 'a' information is in the main query. Any ideas on how I could implement this?



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire