使用 Query Builder 或 Eloquent 进行附加条件的 JOIN [英] A JOIN With Additional Conditions Using Query Builder or Eloquent

问题描述

我正在尝试使用 Laravel Query Builder 的 JOIN 查询添加条件.

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

我知道我可以使用 Raw Expressions 但随后会有 SQL 注入点.我已经使用 Query Builder 尝试了以下操作，但生成的查询(显然，查询结果)不是我想要的:

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

这是 Laravel 生成的查询:

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

如您所见，查询输出没有结构(尤其是在 JOIN 范围内).是否可以在JOIN下添加额外的条件?

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?

如何使用 Laravel 的 Query Builder 构建相同的查询(如果可能)是使用 Eloquent 更好，还是应该使用 DB::select?

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?

推荐答案

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

不确定是否可以在 laravel 中将 between 子句添加到 join 中.

Not quite sure if the between clause can be added to the join in laravel.

注意事项:

• DB::raw() 指示 Laravel 不要放回引号.
• 通过将闭包传递给连接方法，您可以向其添加更多连接条件，on() 将添加 AND 条件和 orOn() 将添加 OR 条件.

• 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.

这篇关于使用 Query Builder 或 Eloquent 进行附加条件的 JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！