Question 17134 in Laravel Question posted in
The official Laravel Documentation can be found here.

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

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!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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();

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search