Cakephp 3 - 'matching' - how to filter the data for the booking system?

Question

I have 'accommod_units' and 'accommod_bookings' table and I want to check the availability of accommodation units for a certain period. For example, Date of arrival: 2019-08-20 and Departure date: 2019-08-27.

'accommodation_units' table is shown below:

'accommod_bookings' table is shown below:

This code does not work:

$this->loadModel('AccommodUnits');            
$this->conditionBookings = [
    'NOT' => [
        [
            'AccommodBookings.date_start >=' => '2019-08-20',
            'AccommodBookings.date_end <=' => '2019-08-27'
        ], 
    ]
];         
$accommodUnits = $this->AccommodUnits
    ->find('all')
    ->distinct(['AccommodUnits.id'])
    ->contain([
        'AccommodBookings' => function ($q) {
            return $q->where($this->conditionBookings);
        },                 
    ])
    ->where($conditionUnits)
    ->matching('AccommodBookings', function ($q) {
        return $q->where($this->conditionBookings);
    });

Their associations are:

$accommodUnits->hasMany('AccommodBookings');

How to solve this problem?

Salines · Answer 1 · 2019-07-18T18:15:34.273

Simple you must overlaps booking periods

[
    'AccommodBookings.date_start <=' => '2019-08-27', // Here put end date greater than start date <=
    'AccommodBookings.date_end >=' => '2019-08-20' // Here put start date smaller than end date  >=
],

Now:

$this->loadModel('AccommodUnits');
$accommodBookings = [
        'AccommodBookings.date_start <=' => '2019-08-27', // not match start date lower than end date
        'AccommodBookings.date_end >=' => '2019-08-20' // not mach end date greater than start date
];

$accommodUnits = $this->AccommodUnits
    ->find() // if empty default is 'all'
    ->where(['AccommodUnits.id' => 124]) // find units 124 where not matching:
    ->notMatching('AccommodBookings', function ($q) use ($accommodBookings) {
        return $q->where($accommodBookings);
    });

RESULT is null if unit 124 has overlaps booking date period.

Using notMatching()

The opposite of matching() is notMatching(). This function will change the query so that it filters results that have no relation to the specified association:

// In a controller or table method.

$query = $articlesTable
    ->find()
    ->notMatching('Tags', function ($q) {
        return $q->where(['Tags.name' => 'boring']);
    });

The above example will find all articles that were not tagged with the word boring.

Poz

Cakephp 3 - 'matching' - how to filter the data for the booking system?

1 Answers1