Un JOIN avec des conditions supplémentaires en utilisant Query Builder ou Eloquent

Question

J'essaie d'ajouter une condition en utilisant une requête JOIN avec Laravel Query Builder.

<?php

$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')
);

Je sais que je peux utiliser Expressions brutes mais il y aura alors des points d'injection SQL. J'ai essayé ce qui suit avec Query Builder mais la requête générée (et évidemment, les résultats de la requête) n'est pas ce que je voulais :

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

C'est la requête générée par 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

Comme vous pouvez le voir, la sortie de la requête n'a pas la structure (surtout sous la portée JOIN). Est-il possible d'ajouter des conditions supplémentaires sous le JOIN ?

Comment puis-je construire la même requête en utilisant le Query Builder de Laravel (si possible) ? Est-il préférable d'utiliser Eloquent, ou faut-il rester avec DB::select ?

Answer 1

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

Je ne suis pas sûr que la clause between puisse être ajoutée à la jointure dans Laravel.

Notes :

• DB::raw() indique à Laravel de ne pas mettre de guillemets.
• En passant une fermeture aux méthodes de jointure, vous pouvez y ajouter d'autres conditions de jointure, on() ajoutera AND l'état et orOn() ajoutera OR condition.

Answer 2

Si vous avez des paramètres, vous pouvez le faire.

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

et ensuite retourner votre requête

retourner $results ;

Answer 3

Vous pouvez reproduire ces crochets dans la jointure gauche :

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

est

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

Vous devrez ensuite définir les liens à l'aide de "setBindings", comme décrit dans ce billet de l'OS : Comment lier des paramètres à une requête DB brute dans Laravel qui est utilisée sur un modèle ?

Ce n'est pas joli mais ça marche.

Answer 4

L'exemple de requête sql est le suivant

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

Jointure Laravel avec plusieurs conditions

->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 5

J'utilise laravel5.2 et nous pouvons ajouter des jointures avec différentes options, vous pouvez modifier selon vos besoins.

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