CakePHP3: setup FOC Search callback

Need Help
1

I need help to setup FOC Search plugin.

I have two models Apartments and Bookings, on Apartments i try to setup FOC Search plugin to check apartment booking status (free, booked,…) between two dates like:

'Bookings.check_out >' => $this->request->data['i'], // check_in
'Bookings.check_in <' => $this->request->data['o'], // check_out

ApartmentsController::availability()

public function availability($slug = null)
{
    
    $apartment = $this->Apartments
    ->find('search', $this->Apartments->filterParams($this->request->query))
    ->contain(['Images','Bookings'])

    /* MOVE this to ApartmentsTable ?
    ->matching('Bookings', function(\Cake\ORM\Query $q){
        return $q->where([
            'Bookings.check_out >' => $this->request->data['i'],
            'Bookings.check_in <' => $this->request->data['o'],
        ]);
    }) */

   ->toArray();
    $this->set('apartment', $apartment);
    $this->set('_serialize', ['apartment']);
    
}

ApartmentsTable

    $this->searchManager()
        ->add('a', 'Search.Like', [
            'field' => [$this->aliasField('id')]
        ])
        ->add('f', 'Search.Like', [
            'before' => false,
            'after' => true,
            'field' => [$this->aliasField('check_in')]
        ])
        ->add('t', 'Search.Like', [
            'before' => false,
            'after' => true,
            'field' => [$this->aliasField('check_out')]
        ]);

    $this->hasMany('Bookings', [
        'foreignKey' => 'apartment_id'
    ]);

Required fields: check_in and check_out , date is stored as DATETIME (2016-05-27 14:00:00)

User can select one apartment from select input or level blank, but must choose two dates (in, out).

url query example?a=1&i=2016-05-25&o=2016-05-27

How to setup FOC Search plugin callback ?

2

Solved

example:

        ->add('i', 'Search.Callback', [
            'field' => [$this->aliasField('check_in')],
            'callback' => function ($query, $args, $manager) {
                return $query->matching('Bookings', function(\Cake\ORM\Query $q) use ($args){
                    return $q->where(function($exp) use ($args){
                        return $exp->gt('Bookings.check_out',$args['i'].' 14:00:00');
                    });
                });
            }
        ])
3

Above query finds Booked periods, but the results are not grouped and look like this:

Apartments A1
- Booking period 1
Apartments A1
- Booking period 2
Apartments A1
- Booking period 3
Apartments A2
- Booking period 1
...

What I need is:

Apartments A1
- Booking period 1
- Booking period 2
- Booking period 3
Apartments A2
- Booking period 1
...

So, all ‘_matchingData’ must be grouped. If i add:

 $apartments = $this->Apartments
    ->find('search', $this->Apartments->filterParams($this->request->query))
    ->group('Apartments.id')

This return only one results from ‘_matchingData’

How to get the data in the desired format?

Thanks

4

You could use groupBy() to group your results after they are fetched:
http://book.cakephp.org/3.0/en/core-libraries/collections.html#Cake\Collection\Collection::groupBy