title: CakePHP searching in multiple HABTM tables
body: <p>My database: I have <em>companies</em> which HABTM <em>carbrands</em> and HABTM <em>goods</em></p>
  
  <p>My search function:</p>
  
  <pre><code>public function search() {
      $joins = array();
      // HABTM
      if (!empty($this-&gt;request-&gt;query['Carbrand'])) {
          $carbrands = array(
              'table' =&gt; 'carbrands_companies',
              'alias' =&gt; 'CarbrandsCompany',
              'type'  =&gt; 'INNER',
              'conditions' =&gt; array(
                  'CarbrandsCompany.carbrand_id'  =&gt; $this-&gt;request-&gt;query['Carbrand'],
                  'CarbrandsCompany.company_id = Company.id'
              ),
          );
          $joins[] = $carbrands;
      }
      if (!empty($this-&gt;request-&gt;query['Good'])) {
          $goods = array(
              'table' =&gt; 'companies_goods',
              'alias' =&gt; 'GoodsCompany',
              'type'  =&gt; 'INNER',
              'conditions' =&gt; array(
                  'GoodsCompany.good_id'  =&gt; $this-&gt;request-&gt;query['Good'],
                  'GoodsCompany.company_id = Company.id'
              ),
          );
          $joins[] = $goods;
      }
      // HABTM END
      $this-&gt;paginate = array(
          'fields' =&gt; array(
              'DISTINCT Company.id',
              'Company.name',
              'Company.addr',
              'Company.official',
          ),
          'limit'      =&gt; 10,
          'joins'      =&gt; $joins,
          'conditions' =&gt; array(),      
          'contain'    =&gt; array(),
          'order'      =&gt; array('Company.name' =&gt; 'asc')         
      );
      $found = $this-&gt;paginate('Company');
      if (!empty($found)) {
          $this-&gt;set('companies', $found);
      }else {
          $this-&gt;render('notfound');
      }
  }
  </code></pre>
  
  <p>It works well except one thing: it provides very strict search. 
  I need to get back all companies that have <em>Carbrand</em> <strong>A</strong> and also all companies that have <em>Good</em> <strong>B</strong>.</p>
  
  <p>If I specify required conditions in my search form, Cake will generate the following SQL:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>And obviously 0 results from database . As far as I can understand this is because my function is very strict and Cake looks for <em>Company</em> which have <em>Carbrand</em> <strong>A</strong> AND have <em>Good</em> <strong>B</strong> at the same time.</p>
  
  <p>But this is not what I need.</p>
  
  <p>The following query fetches desired results:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>How do I change my function to get desired SQL query generated by CakePHP?</p>