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

Need Help
1

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.

'accommod_bookings' table is shown below:

tablica3
tablica3.jpg701×422 99.2 KB

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?

2

Any error you’re getting?

3

between - This is what you are looking for.
More here:

https://book.cakephp.org/chronos/1.x/en/#comparison-methods

4

Hi @lupy ,

For quick fix you can use this code:
$obj = TableRegistry::get(‘accommod_bookings’);
$query = $obj->find(‘list’,
[
‘valueField’ => ‘accommod_unit_id’,
‘conditions’ => [
‘date_start >=’ => ‘2019-09-20’,
‘date_start <=’ => ‘2019-09-27’
]
]);

    		$existingBookings = $query->toList();
    		$obj = TableRegistry::get('accommod_units');
    		$query = $obj->find('all',
    			[
    				'fields'=>['accommod_units.id'],
    				'conditions' => [
    					'NOT'=>[
    						'accommod_units.id IN'=> $existingBookings
    					]
    				]
    			]);
    		foreach ($query as $row) {
    			print_r($row);
    		}
    		exit();

I hope you like my solution.