I have two models, listings and bookings. listings hasmany bookings. I'm trying to write a query to return all listings who DON'T have bookings booked during a certain time period.
I've gotten so far but I'm still a newbie to cakephp and my sql isn't great either. In my controller method I have
$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(
'conditions'=> $conditions,
'joins'=>array(
array(
'table'=>'bookings',
'alias'=>'Booking',
'type' =>'inner',
'conditions' =>array('Booking.listing_id = Listing.id', 'Booking.checkin NOT BETWEEN ? AND ?' => array($to, $from))
)
),
'limit' => 10,
);
$data = $this->paginate();
That just gets all the listings which actually have bookings associated with them. Not only that but it doesn't ignore any bookings which are booked for the given period.
The SQL it creates is:
SELECT
...
FROM
`database`.`listings` AS `Listing`
INNER JOIN
`database`.`bookings` AS `Booking` ON (
`Booking`.`listing_id` = `Listing`.`id` AND
`Booking`.`checkin` NOT BETWEEN '18-07-2013' AND '10-07-2013'
)
LEFT JOIN
`database`.`users` AS `User` ON (
`Listing`.`user_id` = `User`.`id`
)
WHERE (
(`Listing`.`address_one` LIKE '%belfast%') OR
(`Listing`.`address_two` LIKE '%belfast%') OR
(`Listing`.`city` LIKE '%belfast%') OR
(`Listing`.`postcode` LIKE '%belfast%') OR
(`Listing`.`title` LIKE '%belfast%')
)
LIMIT 10
Which looks kinda rightish(?) but obviously isn't. Anyone any ideas? Thanks