A JOIN With Additional Conditions Using Query Builder or Eloquent
laravel, laravel-4, laravel-query-builder
Solution
$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();
Not quite sure if the between clause can be added to the join in laravel.
Notes:
- `DB::raw()` instructs Laravel not to put back quotes.
- By passing a closure to join methods you can add more join conditions to it, `on()` will add `AND` condition and `orOn()` will add `OR` condition.
Problem
I'm trying to add a condition using a JOIN query with 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') ); ``` I know I can use Raw Expressions but then there will be SQL injection points. I've tried the following with Query Builder but the generated query (and obviously, query results) aren't what I intended: ``` $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(); ``` This is the generated query by 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 ``` As you can see, the query output doesn't have the structure (especially under JOIN scope). Is it possible to add additional conditions under the JOIN? How can I build the same query using Laravel's Query Builder (if possible) Is it better to use Eloquent, or should stay with DB::select?