title: CakePHP - Searching 3 tables efficiently using JOIN
body: <p>I'm currently trying to implement a search engine function in my CakePHP site, trying to return information from 3 tables efficiently. The main usage will be numeric searches, free text will be extremely minimal and as such I'm not trying to optimise for this scenario.</p>
  
  <pre><code>Companies hasMany Products
  Products hasMany Prices
  </code></pre>
  
  <p>Ideally I'd like to be able to use something like the following function in the Products controller (this won't work due to distant relationships):</p>
  
  <pre><code>$results = $this-&gt;Product-&gt;find('all', array(
      //conditions can be defined against all 3 tables
      'conditions' =&gt;. array(
          'company.name LIKE' =&gt; '%'.$search_term.'%',
          'product.feature' =&gt; $product_feature,
          'price.price &lt;' =&gt; $price
      ),
      //fields restricted against all 3 tables
      'fields' =&gt; array(
          'company.name',
          'product.feature',
          'price.price'
      )
  ));
  </code></pre>
  
  <p>I've tried using Containable behaviour to include the three models but to no avail.</p>
  
  <p>I believe the solution lies with JOINS but my experience with these is limited, I've tried code similar to the following within the find function above:</p>
  
  <pre><code>'joins' =&gt; array(
      array(
          'table' =&gt; 'companies',
          'alias' =&gt; 'Company',
          //tried a mix of joins (LEFT, RIGHT, INNER)
          'type' =&gt; 'LEFT',
          'conditions' =&gt; array(
              'Company.id = Product.company_id'
          )
      ),
      array(
          'table' =&gt; 'prices',
          'alias' =&gt; 'Price',
          //tried a mix of joins (LEFT, RIGHT, INNER)
          'type' =&gt; 'LEFT',
          'conditions' =&gt; array(
              'Price.product_id = Product.id'
          )
      ),
  ),
  'recursive' =&gt; 1,
  </code></pre>
  
  <p>Edit: The result of the above joins is that when i specify the price in the conditions or fields that it can't be found, I tried changing the names such as Product.Price.price to account for the one to many relationship but still without luck.</p>
  
  <p>I'd appreciate any help in finding a solution!</p>