Laravel 8 - How I do where clause in table added with join-CodePudding

Hi I want to know how can i do this query in Laravel 8 , I tried adding the join clause but not work as expected, i need join clause? Or maybe there is another form to do it. I search others examples but i don´t see anythat help me. The query is the next:

DB::table('escandallo_p as esc')
    ->select("esc.material", "esc.referencia", "esc.ancho", "esc.proveedor", "esc.punto", 
             "esc.precio", "esc.consumo", "esc.veces", "esc.001", "esc.002", "esc.003", "esc.004", 
             "esc.005", "esc.006", "esc.007", "esc.008", "esc.009", "esc.010", "esc.011", "esc.um", "esc.merma", "esc.importe", "esc.tipo", "esc.xtalla", "esc.fase", 
              DB::raw("(select anulado from prototipos_p as p where p.prototipo = '".$scandal[0]->prototipo."' and p.tipo = 'c' and p.referencia = esc.referencia )"),
       // ignore             
      //original query "(select anulado from prototipos_p as p where p.prototipo = ",$request->prototipo," and p.tipo = 'c' and p.referencia = esc.referencia ) as 'anulado'", 
     //                "(select clase from prototipos_p as p where p.prototipo = ",$request->prototipo," and p.tipo = 'c' and p.referencia = esc.referencia ) as 'clase'")
    //Converted query   ->select('pro.anulado')->where('pro.prototipo', $request->prototipo)
   //                   ->where("p.prototipo", "=", $request->prototipo)
    ->where("esc.id_escandallo", "=", $request->id_escandallo)
    ->where("esc.id_version", "=", $request->version)
    ->orderBy("id","asc")
    ->get();

!!!! I need to pass the esc.referencia to the sub select query

The second select is the conversion of the select inside "" ( i know this is wrong is only for explain it).

Thank you in advance for any suggestion.

Best regards

EDIT: I can solve my problem with DB::raw, but if anyone know others methos are welcome!

CodePudding user response：

You need to pass callback to the join query to add the extra query to the laravel's join method, Example from Laravel Doc:

DB::table('users')
    ->join('contacts', function ($join) {
        $join->on('users.id', '=', 'contacts.user_id')
             ->where('contacts.user_id', '>', 5);
    })
    ->get();

It is explained in Laravel's doc, Advanced Join Clauses

There is Subquery support too Subquery Joins, Eg:

$latestPosts = DB::table('posts')
        ->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
        ->where('is_published', true)
        ->groupBy('user_id');

    $users = DB::table('users')
        ->joinSub($latestPosts, 'latest_posts', function ($join) {
            $join->on('users.id', '=', 'latest_posts.user_id');
        })
        ->get();

These two might help you to achieve what you are trying

CodePudding user response：

After test joins, joinSub, whereIn and other forms of doing this, I solved my problem using the DB::raw():

DB::table('escandallo_p as esc')
            ->select('parameters',....,
                     DB::raw("(SELECT column //(ONLY ONE) 
                               FROM table 
                               WHERE column = '".$parameter."' ...) AS nombre"),
                    )
            ->where('column', "=", $parameter)
            ->orderBy("id","asc")
            ->get();