mysql - CakePHP 在多个 HABTM 表中搜索

Question

我的数据库:我有公司，其中HABTM汽车品牌和HABTM商品

我的搜索功能:

public function search() {
    $joins = array();
    // HABTM
    if (!empty($this->request->query['Carbrand'])) {
        $carbrands = array(
            'table' => 'carbrands_companies',
            'alias' => 'CarbrandsCompany',
            'type'  => 'INNER',
            'conditions' => array(
                'CarbrandsCompany.carbrand_id'  => $this->request->query['Carbrand'],
                'CarbrandsCompany.company_id = Company.id'
            ),
        );
        $joins[] = $carbrands;
    }
    if (!empty($this->request->query['Good'])) {
        $goods = array(
            'table' => 'companies_goods',
            'alias' => 'GoodsCompany',
            'type'  => 'INNER',
            'conditions' => array(
                'GoodsCompany.good_id'  => $this->request->query['Good'],
                'GoodsCompany.company_id = Company.id'
            ),
        );
        $joins[] = $goods;
    }
    // HABTM END
    $this->paginate = array(
        'fields' => array(
            'DISTINCT Company.id',
            'Company.name',
            'Company.addr',
            'Company.official',
        ),
        'limit'      => 10,
        'joins'      => $joins,
        'conditions' => array(),      
        'contain'    => array(),
        'order'      => array('Company.name' => 'asc')         
    );
    $found = $this->paginate('Company');
    if (!empty($found)) {
        $this->set('companies', $found);
    }else {
        $this->render('notfound');
    }
}

它工作得很好，除了一件事:它提供了非常严格的搜索。我需要取回所有拥有 Carbrand A 的公司以及所有拥有 Good B 的公司。

如果我在搜索表单中指定所需条件，Cake 将生成以下 SQL:

 SELECT DISTINCT `Company`.`id`, `Company`.`name`, `Company`.`addr`, `Company`.`official` FROM `autov`.`companies` AS `Company` 
 INNER JOIN `autov`.`carbrands_companies` AS `CarbrandsCompany` ON (`CarbrandsCompany`.`carbrand_id` = ('23') AND `CarbrandsCompany`.`company_id` = `Company`.`id`) 
 INNER JOIN `autov`.`companies_goods` AS `GoodsCompany` ON (`GoodsCompany`.`good_id` = ('1') AND `GoodsCompany`.`company_id` = `Company`.`id`) 
 WHERE 1 = 1 ORDER BY `Company`.`name` asc 
 LIMIT 10

显然数据库中的结果为 0。据我所知，这是因为我的职能非常严格，Cake 寻找具有 Carbrand A 且具有 Good 的 Company B 同时。

但这不是我需要的。

以下查询获取所需结果:

SELECT DISTINCT `Company`.`id`, `Company`.`name`, `Company`.`addr`, `Company`.`official`         
FROM `auto`.`companies` AS `Company` 
INNER JOIN `auto`.`carbrands_companies` AS `CarbrandsCompany` ON
(`CarbrandsCompany`.`carbrand_id` = ('23') AND `CarbrandsCompany`.`company_id` = `Company`.`id`)

UNION

SELECT DISTINCT `Company`.`id`, `Company`.`name`, `Company`.`addr`, `Company`.`official` FROM `auto`.`companies` AS `Company` 
INNER JOIN `auto`.`companies_goods` AS `GoodsCompany` ON (`GoodsCompany`.`good_id` = ('1') AND `GoodsCompany`.`company_id` = `Company`.`id`) 
WHERE 1 = 1
LIMIT 100

如何更改我的函数以获得由 CakePHP 生成的所需 SQL 查询？

Answer 1

听起来您正在寻找的是:

SELECT DISTINCT `Company`.`id`, `Company`.`name`, `Company`.`addr`, `Company`.`official`    
FROM `autov`.`companies` AS `Company` 
INNER JOIN `autov`.`carbrands_companies` AS `CarbrandsCompany` 
ON `CarbrandsCompany`.`company_id` = `Company`.`id`
INNER JOIN `autov`.`companies_goods` AS `GoodsCompany` 
ON `GoodsCompany`.`company_id` = `Company`.`id`
WHERE `CarbrandsCompany`.`carbrand_id` = 23  OR
`GoodsCompany`.`good_id` = 1
ORDER BY `Company`.`name` asc 
LIMIT 10

换句话说，我认为如果您将每个联接上的联接条件移至 $this->paginate['conditions'] 中，它应该可以工作:

$this->paginate['conditions'] => array('OR' => array('GoodsCompany.good_id' => 1,  'CarbrandsCompany.carbrand_id' => 23));