CakePHP searching in multiple HABTM tables

Question

My database: I have companies which HABTM carbrands and HABTM goods

My search function:

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

It works well except one thing: it provides very strict search. I need to get back all companies that have Carbrand A and also all companies that have Good B.

If I specify required conditions in my search form, Cake will generate the following 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

And obviously 0 results from database . As far as I can understand this is because my function is very strict and Cake looks for Company which have Carbrand A AND have Good B at the same time.

But this is not what I need.

The following query fetches desired results:

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

How do I change my function to get desired SQL query generated by CakePHP?

@Dave I hope to find where is my mistake and how to change my function to be able to get the results that I need. — user1327, Dec 10 '13 at 19:22
I'm not asking your general hopes and dreams - I'm asking - what is the actual MySQL query you're hoping CakePHP to generate for you. If you can do that, it will be very easy for us to help you determine what is different than what it's actually generating and why. — Dave, Dec 10 '13 at 19:25
@Dave query that Cake generates for me is what my function tells to generate. Sorry but I don't know how to fetch results that I need using SQL, so I can't provide you an example. If I use only one inner join e.g. I'm only looking for 'brands' everything works well. Same for 'goods'. But I need a joint result from both = be able to list all companies that have 'brand' A and all also companies that have 'good' B. — user1327, Dec 10 '13 at 19:34
Write the query so you get your desired results in MySQL (phpMyAdmin, HeidiSQL, Navicat, '->query()'...etc), then show that here. — Dave, Dec 10 '13 at 19:41

score 0 · Answer 1 · answered Dec 10 '13 at 18:59

It sounds like what you're looking for is:

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

In other words, I think if you move the join conditions on each of the joins into $this->paginate['conditions'] instead, it should work:

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

CakePHP searching in multiple HABTM tables

1 Answers1