php - Eloquent SQL查询表示

你好，我在我的项目中使用了slim的雄辩。我已经创建了模型：reservation、reservationcoutt（linking table）、couth with relations:reservations to coutages like n-n，现在我想知道如何更改我的sql查询以获得雄辩的查询。

class Reservation extends Model
{
    protected $table = 'reservations';
    protected $fillable = [
        'start',
        'end',
    ];
    public function cottages()
    {
        return $this->belongsToMany(Cottage::class);
    }
    public function user()
    {
        return $this->belongsTo(User::class);
    }
    public function reservationStatus()
    {
        return $this->belongsTo(ReservationStatus::class);
    }
    public function payments()
    {
        return $this->hasMany(Payment::class);
    }
}

class ReservationCottage extends Model
{
    protected $table = 'reservation_cottages';

    public function guests()
    {
        return $this->hasMany(Guest::class);
    }
}

class Cottage extends Model
{
    protected $table = 'cottages';

    protected $fillable = [
        'name',
        'capacity',
        'description',
        'base_price',
    ];

    public function additions()
    {
        return $this->belongsToMany(Addition::class);
    }
    public function cottagePeriods()
    {
        return $this->hasMany(CottagePeriod::class);
    }
    public function periods()
    {
        return $this->belongsToMany(Period::class);
    }
    public function reservations()
    {
        return $this->belongsToMany(Reservation::class);
    }
    public function cottageStatus()
    {
        return $this->belongsTo(CottageStatus::class);
    }

这是查询wchih check period（从@ArrivalDate到@DepartureDate）是否在reservations表中可用：

SELECT cottage.name FROM cottages WHERE id NOT IN (
    SELECT cottage_id
    FROM   reservation_cottages RC
           JOIN reservations R
               ON R.id = RC.reservation_id
    WHERE  (r.start <= @ArrivalDate AND R.end >= @ArrivalDate)
           OR (R.start < @DepartureDate AND R.end >= @DepartureDate )
           OR (@ArrivalDate <= R.start AND @DepartureDate >= R.start)

最佳答案

好的，这里有一个（希望）更有用的答案。在我的示例中，我使用laravel查询生成器创建这样一个复杂的查询。例子：

$rows = DB::table('cottages')
    ->select('cottage.name')
    ->from('cottages')
    ->whereNotIn('id', function (Builder $query) {
        $query->select('cottage_id')
            ->from('reservation_cottages AS RC')
            ->join('reservations AS R', 'R.id', '=', 'RC.reservation_id')
            ->whereColumn('r.start', '<=', 'ArrivalDate')
            ->whereColumn('R.end', '>=', 'ArrivalDate')
            ->orWhere(function (Builder $query) {
                $query->whereColumn('R.start', '<', 'DepartureDate')
                    ->whereColumn('R.end', '>=', 'DepartureDate');
            })
            ->orWhere(function (Builder $query) {
                $query->whereColumn('ArrivalDate', '<=', 'R.start')
                    ->whereColumn('DepartureDate', '>=', 'R.start');
            });
    })->get();

生成的SQL:

SELECT
  `cottage`.`name`
FROM
  `cottages`
WHERE `id` NOT IN
(SELECT
    `cottage_id`
    FROM
    `reservation_cottages` AS `RC`
    INNER JOIN `reservations` AS `R` ON `R`.`id` = `RC`.`reservation_id`
    WHERE `r`.`start` <= `ArrivalDate`
    AND `R`.`end` >= `ArrivalDate`
    OR (
        `R`.`start` < `DepartureDate`
        AND `R`.`end` >= `DepartureDate`
       )
    OR (
        `ArrivalDate` <= `R`.`start`
        AND `DepartureDate` >= `R`.`start`
));

关于php - Eloquent SQL查询表示，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48388301/