title: Translate this monsterous query w/HABTM related data into a CakePHP find
body: <p>It's late and I have written this monstrosity of a query to get related products based on a product I have already found.</p>
  
  <p>I need to fetch the products in the same category (HABTM), the parent product, products with the same parent (siblings/neighbours), and products that are direct children of the current product (there is only one level of nesting). I have the product ID and its parent_id of the current product. If it could be possible to put conditions on the product as for <code>Product.published = 1</code> that would be great, but if it's going to make the query so big I can always check that after. Additionally, I need to exclude the current product.</p>
  
  <pre><code>SELECT `products`.*
  FROM `products`, `categories_products`
  WHERE
  (
      (
          `categories_products`.`product_id` = `products`.`id`
          AND `categories_products`.`category_id` IN (
              SELECT `category_id`
              FROM `categories_products`
              WHERE `categories_products`.`product_id` = '$product_id'
          )
      )
      OR `products`.`parent_id` = '$parent_id'
      OR `products`.`parent_id` = '$product_id'
      OR `products`.`id` = '$parent_id'
  )
  AND `product`.`id` &lt;&gt; '$product_id'
  
  GROUP BY `products`.`id`
  </code></pre>
  
  <p>It might even be possible to optimize it a bit more, so far I have:</p>
  
  <pre><code>public function related($productData, $limit = 4) {
  
      $conditions = array(
          'OR' =&gt; array(array('Product.parent_id' =&gt; $productData['Product']['id'])), // Children of product),
          'Product.id &lt;&gt;' =&gt; $productData['Product']['id']
      );
  
      if(!empty($product['parent_id'])) {
          $conditions['OR'][] = array('Product.parent_id' =&gt; $productData['Product']['parent_id']); // Siblings
          $conditions['OR'][] = array('Product.id' =&gt; $productData['Product']['parent_id']); // Parent of product
      }
  
      return $this-&gt;find('all', array(
          'conditions' =&gt; $conditions,
          'contain' =&gt; array('Category'),
          'group' =&gt; 'Product.id',
          'limit' =&gt; $limit
      ));
  }
  </code></pre>