laravel - left - Unir con condición adicional utilizando Query Builder o Eloquent

query builder laravel

Question

Intento agregar una condición en la consulta de unión utilizando el generador de consultas de Laravel.

Esta es la consulta que estoy tratando de reescribir con QB:

 $results = DB::select(''
       SELECT DISTINCT 
          *
          FROM 
             rooms 
                LEFT JOIN bookings  
                   ON rooms.id = bookings.room_type_id
                  AND (  bookings.arrival between ? and ?
                      OR bookings.departure between ? and ? )
          WHERE
                bookings.room_type_id IS NULL
          LIMIT 20'', 
        array(''2012-05-01'', ''2012-05-10'', ''2012-05-01'', ''2012-05-10'')
     );

Sé que puedo usar Raw Ex [ressions ( http://four.laravel.com/docs/queries#raw-expressions ), pero luego habrá puntos de inyección SQL. He intentado lo siguiente (con el generador de consultas), pero la consulta generada (y obviamente, los resultados de la consulta) no son lo que pretendía:

         $results = DB::table(''rooms'')
                     ->distinct()
                     ->leftJoin(''bookings'', function($join)
                         {
                             $join->on(''rooms.id'', ''='', ''bookings.room_type_id'');

                         })
                        ->whereBetween(''arrival'', array(''2012-05-01'', ''2012-05-10''))
                        ->whereBetween(''departure'', array(''2012-05-01'', ''2012-05-10''))
                     ->where(''bookings.room_type_id'', ''='', NULL)
                     ->get();

Esta es la consulta generada por Laravel:

  select distinct * from `room_type_info`
  left join `bookings` 
  on `room_type_info`.`id` = `bookings`.`room_type_id` 
   where `arrival` between ? and ? 
   and `departure` between ? and ? 
  and `bookings`.`room_type_id` is null

Como puede ver, la consulta generada no tiene la estructura (especialmente en el alcance de JOIN. ¿Es posible agregar condiciones adicionales al unirse en QB?).

¿Cómo puedo construir la misma consulta usando el Query Builder de Laravel (si es posible)? ¿Es mejor usar Eloquent, o debería quedarse con DB :: select?

Answer 1

Estoy usando laravel5.2 y podemos agregar combinaciones con diferentes opciones, puede modificar según su requisito.

Option 1:    
    DB::table(''users'')
            ->join(''contacts'', function ($join) {
                $join->on(''users.id'', ''='', ''contacts.user_id'')->orOn(...);//you add more joins here
            })// and you add more joins here
        ->get();

Option 2:
    $users = DB::table(''users'')
        ->join(''contacts'', ''users.id'', ''='', ''contacts.user_id'')
        ->join(''orders'', ''users.id'', ''='', ''orders.user_id'')// you may add more joins
        ->select(''users.*'', ''contacts.phone'', ''orders.price'')
        ->get();

option 3:
    $users = DB::table(''users'')
        ->leftJoin(''posts'', ''users.id'', ''='', ''posts.user_id'')
        ->leftJoin(''...'', ''...'', ''...'', ''...'')// you may add more joins
        ->get();

Answer 2

Hay una diferencia entre las consultas crudas y las selecciones estándar (entre los métodos DB::raw y DB::select ).

Puede hacer lo que quiera usando un DB::select y simplemente colocar en el ? marcador de posición como lo hace con las declaraciones preparadas (en realidad es lo que está haciendo).

Un pequeño ejemplo:

$results = DB::select(''SELECT * FROM user WHERE username=?'', [''jason'']);

El segundo parámetro es una matriz de valores que se utilizará para reemplazar los marcadores de posición en la consulta de izquierda a derecha.

Answer 3

Puede replicar los paréntesis en la combinación izquierda:

LEFT JOIN bookings  
               ON rooms.id = bookings.room_type_id
              AND (  bookings.arrival between ? and ?
                  OR bookings.departure between ? and ? )

es

->leftJoin(''bookings'', function($join){
    $join->on(''rooms.id'', ''='', ''bookings.room_type_id'');
    $join->on(DB::raw(''(  bookings.arrival between ? and ? OR bookings.departure between ? and ? )''), DB::raw(''''), DB::raw(''''));
})

Luego tendrá que establecer los enlaces más tarde usando "setBindings" como se describe en esta publicación de SO: ¿Cómo enlazar parámetros a una consulta de DB sin procesar en Laravel que se usa en un modelo?

No es bonito, pero funciona.

Answer 4

Si tiene algunos params, puede hacer esto.

    $results = DB::table(''rooms'')
    ->distinct()
    ->leftJoin(''bookings'', function($join) use ($param1, $param2)
    {
        $join->on(''rooms.id'', ''='', ''bookings.room_type_id'');
        $join->on(''arrival'',''='',DB::raw("''".$param1."''"));
        $join->on(''arrival'',''='',DB::raw("''".$param2."''"));

    })
    ->where(''bookings.room_type_id'', ''='', NULL)
    ->get();

y luego devuelve tu consulta

devolver $ resultados;

Answer 5

El ejemplo de consulta SQL como este

LEFT JOIN bookings  
    ON rooms.id = bookings.room_type_id
    AND (bookings.arrival = ?
        OR bookings.departure = ?)

Laravel se une con múltiples condiciones

->leftJoin(''bookings'', function($join) use ($param1, $param2) {
    $join->on(''rooms.id'', ''='', ''bookings.room_type_id'');
    $join->on(function($query) use ($param1, $param2) {
        $query->on(''bookings.arrival'', ''='', $param1);
        $query->orOn(''departure'', ''='',$param2);
    });
})

Answer 6

$results = DB::table(''rooms'')
                     ->distinct()
                     ->leftJoin(''bookings'', function($join)
                         {
                             $join->on(''rooms.id'', ''='', ''bookings.room_type_id'');
                             $join->on(''arrival'',''>='',DB::raw("''2012-05-01''"));
                             $join->on(''arrival'',''<='',DB::raw("''2012-05-10''"));
                             $join->on(''departure'',''>='',DB::raw("''2012-05-01''"));
                             $join->on(''departure'',''<='',DB::raw("''2012-05-10''"));
                         })
                     ->where(''bookings.room_type_id'', ''='', NULL)
                     ->get();

No estoy seguro si la cláusula between se puede agregar a la unión en laravel.

Notas:

• DB :: raw () ordena a Laravel que no devuelva las comillas.
• Al pasar un cierre para unir métodos puede agregar más condiciones de unión, on () agregará AND condition y orOn () agregará O condición.