Databases
StackOverflow2013
Postsdbo
POCakePHP searching in multiple HABTM tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCakePHP searching in multiple HABTM tables
    primarykey
    data
    text
    <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>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1327
    1. USuser1327
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat is the SQL you're hoping to get?
      singulars
      1. POCakePHP searching in multiple HABTM tables
      1. USDave
    2. CO@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.
      singulars
      1. POCakePHP searching in multiple HABTM tables
      1. USuser1327
    3. COI'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.
      singulars
      1. POCakePHP searching in multiple HABTM tables
      1. USDave
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload