cakephp hasmany query issue

Question

I have a three tables which have associations as follows: listings hasmany bookings listings hasmany special_prices

Tables:

bookings.id, bookings.start, bookings.stop, bookings.listing_id

special_prices.id, special_prices.start, special_prices.stop, special_prices.price, special_prices.listing_id,

And of course the Listings table.

The associations are all set up in the Model file courtesy of cake bake. Users are able to search all the listings according to area, a start date and a finish date. I'm trying get a set of results where only listings are returned if they don't have a start date which is BETWEEN the dates of any booking that listing might have.

In my search function I have:

$conditions = array(
"AND" => array(
    "OR" => array(
                "Listing.address_one LIKE" => "%".$area."%",
                "Listing.address_two LIKE" => "%".$area."%",
                "Listing.city LIKE" => "%".$area."%",
                "Listing.postcode LIKE" => "%".$area."%",
                "Listing.title LIKE" => "%".$area."%",
                ),
                )
            );

$this->Listing->bindModel(array('hasMany' => array('Booking' => array('conditions' =>array('Booking.checkin BETWEEN ? AND ?' => array($to, $from))))));
$data = $this->paginate('Listing', $conditions);
$this->set('data', $data);

I read here: http://ibndawood.com/2011/10/how-to-filter-hasmany-related-model-records-in-cakephp-when-using-find-function/ that the other option is to use

$this->Listing->hasMany['Booking']['conditions'] = array('Booking.checkin BETWEEN ? AND ?' => array($to, $from));

But unfortunately both these just return all the entries in the database which match the area but completely ignores the Bookings table it seems. I've tried making the condition

'Booking.id' => '5'

To test, but it ignores this too.

Can anyone shed some light on this for me. There's not too many examples in the documentation and what's there ain't working for me.

EDIT

I've managed to find out that because cakephp doesn't automatically join hasmany relationships when you are paginating, therefore I had to add some code to tell it to join the bookings table.

Now it seems to be joining alright with the Bookings table but it's returning multiple sets of Listings. Can anyone take a look and help me out with this? Here's what I have now:

$conditions = array(
                            "OR" => array(
                                "Listing.address_one LIKE" => "%".$area."%",
                                "Listing.address_two LIKE" => "%".$area."%",
                                "Listing.city LIKE" => "%".$area."%",
                                "Listing.postcode LIKE" => "%".$area."%",
                                "Listing.title LIKE" => "%".$area."%",
                            )
                        );


    $this->paginate = array(
               'joins'=>array(
                          array(
                           'table'=>'bookings',
                           'alias'=>'Booking',
                           'type' =>'inner',
                           'conditions' =>array('Booking.checkin NOT BETWEEN ? AND ?' => array($to, $from))
                          )
                        ),
                'conditions'=> $conditions
                );

    $data = $this->paginate();

Has anyone any idea about this? I'm banging my head off the table :( — Zaphod Beeblebrox

Alex Stallen Alex Stallen · Accepted Answer · 2013-07-08T13:45:15

Booking is already bound to Listing in the models right? then there is no need to use bindModel on the fly.

just add your Booking.checkin BETWEEN $to AND $from into the $conditions array

cakephp hasmany query issue

1 Answers