php - 在 cakephp 中查询所需的 mySql 建议

Question

我有两个模型，listings 和 bookings。房源有很多预订。我正在尝试编写查询以返回在特定时间段内没有预订的所有列表。

到目前为止我已经了解了，但我仍然是 cakephp 的新手，而且我的 sql 也不是很好。在我的 Controller 方法中我有

$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();

这只会获取所有实际与预订相关联的列表。不仅如此，它还不会忽略在给定时间段内预订的任何预订。

它创建的 SQL 是:

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

这看起来有点正确(？)但显然不是。有人有什么想法吗？谢谢

Answer 1

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

生成适当查找的第一步是了解所需的查询类型 - 即从数据库开始。

问题中的查询正在查找预订超出日期范围的所有列表(匹配条件)。 IE。在任何时间预订贝尔法斯特，BETWEEN '18-07-2013' AND '10-07-2013' 除外。这和上面那句话的逻辑不一样。

有多种方法可以实现，这里按易用性顺序列出两种:

使用左连接

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

即加入日期与日期范围匹配的预订，并且只返回没有预订的记录。

实现上面的查询很容易:

function index() {
    ... # Existing code

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

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

使用 NOT EXISTS

或者，使用 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

这种查询更合乎逻辑，好处是它是一个有意义的查询“在没有 (...) 的地方找到这些”。它可能性能更好，但对于任何查询，最好在选择一个或另一个之前分析/解释查询。

实现上面的查询is a little more involved in CakePHP但这并不难:

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);
}