title: CakePHP -- limiting results from one model to those with matching conditions in an associated model
body: <p>I'm just getting started in CakePHP, and am running into trouble limiting retrieved results to those where a field in an associated model matches a certain value. I've found a lot of related questions here and elsewhere, but they seem to deal with situations that are either more complicated or more simple than my scenario, so here we go:</p>
  
  <p>I have two models that I'm interested in: <strong>Image</strong>, and <strong>Collection</strong>, which are associated by a <strong>HABTM</strong> relationship. The relationship is set up in Image.php and Collection.php correctly through join table <strong>collections_images</strong>, and I've had no issue writing or retrieving data in general. <strong>Containable</strong> is also set up properly in the Image model.</p>
  
  <p>What I would like to do is run find() on $this->Image, and return only those that belong to a particular collection, whose title is <strong>$collection</strong>. I have tried this a couple different ways:</p>
  
  <pre><code>$this-&gt;Image-&gt;contain("Collection"); // to exclude other models "Image" is related to
  
  pr($this-&gt;Image-&gt;find("all", 
                         array("conditions" =&gt; array("Collection.title" =&gt; $collection))
                        ));
  </code></pre>
  
  <p>For this, I get the following error:</p>
  
  <blockquote>
    <blockquote>
      <p>Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Collection.title' in 'where clause'</p>
    </blockquote>
  </blockquote>
  
  <p>It seems you cannot directly establish a search condition through a related model, so I went back and tried to limit my <strong>contain</strong> as follows:</p>
  
  <pre><code>$this-&gt;Image-&gt;contain(array(
                            "Collection" =&gt; array(
                                "conditions" =&gt; array(
                                    "Collection.title" =&gt; $collection))));
  
  pr($this-&gt;Image-&gt;find("all"));
  </code></pre>
  
  <p>This time I get results, but they include <strong>Images</strong> that do not belong to the right collection. Here is a simplified snippet that the pr() gives me for <strong>$collection = "Urban"</strong>:</p>
  
  <pre><code>[3] =&gt; Array
      (
          [Image] =&gt; Array
              (
                  [id] =&gt; 39
                  [title] =&gt; Star Trails over Greenlake
              )
  
          [Collection] =&gt; Array
              (
              )
      )
  
  [4] =&gt; Array
      (
          [Image] =&gt; Array
              (
                  [id] =&gt; 40
                  [title] =&gt; Aurora Bridge and Reflections
  
              )
  
          [Collection] =&gt; Array
              (
                  [0] =&gt; Array
                      (
                          [id] =&gt; 3
                          [title] =&gt; Urban
                          [CollectionsImage] =&gt; Array
                              (
                                  [id] =&gt; 62
                                  [collection_id] =&gt; 3
                                  [image_id] =&gt; 40
                              )
  
                      )
  
              )
  
      )
  </code></pre>
  
  <p>As you can see, it is omitting <strong>Collection</strong> data for the <strong>Images</strong> where the <strong>Collection</strong> title does not match <strong>$collection</strong>, but it is still including the <strong>Image</strong> data.</p>
  
  <p>What am I missing here? Is there a way to make either of these two approaches do what I'm aiming for?</p>
  
  <p>For the record, I have also gone about it from the other direction:</p>
  
  <pre><code>$this-&gt;Collection-&gt;find("all", array("conditions" =&gt; array("Collection.title" =&gt; $collection)))
  </code></pre>
  
  <p>which returns just the desired data. However, the array structure of the data it returns creates complications elsewhere in my code, and I'd like to eventually be able to paginate directly off of <strong>Image</strong>.</p>
  
  <p>Thanks for your time and help, everyone.</p>
  
  <p><strong>EDIT</strong>:</p>
  
  <p><em>After additional searching, I've found an acceptable solution here:</em></p>
  
  <p><a href="http://cakebaker.42dh.com/2007/10/17/pagination-of-data-from-a-habtm-relationship/" rel="nofollow">http://cakebaker.42dh.com/2007/10/17/pagination-of-data-from-a-habtm-relationship/</a></p>
  
  <p><em>This required me to create a model for the mapping table <strong>"CollectionsImage.php"</strong> and establish <strong>hasOne</strong> relationships to both <strong>Image</strong> and <strong>Collection</strong>. The paginate() is then performed on <strong>CollectionsImage</strong>, like so:</em></p>
  
  <pre><code>$data = $this-&gt;paginate("CollectionsImage", array("Collection.id" =&gt; $collection_id));
  </code></pre>
  
  <p><em>This seems a little roundabout and requires an extra step for me in finding the id for a collection with a specified title, so I'm still wondering if anyone knows a more direct solution.</em></p>