mySql advice needed for query in cakephp

Question

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

AD7six AD7six · Accepted Answer · 2013-07-09T08:55:05

return all listings who DON'T have bookings booked during a certain time period

The first step in generating an appropriate find, is to understand the sort of query that is required - i.e. start at the database.

The query in the question is looking for all listings (matching conditions) with a booking that is outside the range of dates. I.e. a booking for belfast at any time except BETWEEN '18-07-2013' AND '10-07-2013'. That is not the same logic as the sentence above.

There are multiple ways to achieve that, here are two in order of ease of implementation:

Use a left join

SELECT 
    *
FROM 
    `nights2stay`.`listings` AS `Listing` 
LEFT JOIN                                                             # <-
    `nights2stay`.`bookings` AS `Booking` ON (
        `Booking`.`listing_id` = `Listing`.`id` AND
        `Booking`.`checkin` BETWEEN '18-07-2013' AND '10-07-2013'     # <-
     ) 
LEFT JOIN 
    `nights2stay`.`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%')
    ) AND
    `Booking`.`id` IS NULL                                            # <-
LIMIT 10

I.e. join bookings with dates matching the date range, and only return records where there is not a booking.

To achieve the above query is very easy:

function index() {
    ... # Existing code

    $this->paginate['joins'][0]['type'] = 'LEFT';
    $this->paginate['conditions']['Booking.id'] = null;
    $data = $this->paginate();

    $this->set('data', $data);
}

Use NOT EXISTS

Alternatively, use NOT EXISTS:

SELECT 
    *
FROM 
    `nights2stay`.`listings` AS `Listing`
                                                                      # <
LEFT JOIN 
    `nights2stay`.`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%')
    ) AND
    NOT EXISTS (                                                      # <
        SELECT                                                        # <
            *                                                         # <
        FROM                                                          # <
            `nights2stay`.`bookings` AS `Booking`                     # <
        WHERE                                                         # <
            `Booking`.`listing_id` = `Listing`.`id` AND               # <
            `Booking`.`checkin` BETWEEN '18-07-2013' AND '10-07-2013' # <
    )                                                                 # <
LIMIT 10

This kind of query is more logical, and has the benefit that it is a meaningful query "find me these where there are no (...)". It will probably perform better, though as with any query it's best to profile/explain queries before choosing one or the other.

To achieve the above query is a little more involved in CakePHP but it's not difficult:

function index() {
    ... 

    $db = $this->Listing->getDatasource();
    $subQuery = $db->buildStatement(array(
        'fields'     => "*",
        'table'      => $db->fullTableName('bookings'),
        'alias'      => 'Booking'
        'conditions' => array(
            '`Booking`.`listing_id` = `Listing`.`id`',
            "`Booking`.`checkin` BETWEEN '$to' AND '$from'"
        )
    ));

    $expression = $db->expression('NOT EXISTS (' . $subQuery . ')');

    $this->paginate['conditions'][] = $expression;
    $data = $this->paginate();

    $this->set('data', $data);
}

mySql advice needed for query in cakephp

1 Answers

Use a left join

Use NOT EXISTS