title: CakePHP Retrieving data from multiple tables with select conditions
body: <p>I am building an application using <code>CakePHP</code> and I am stuck on a problem retrieving data using a series of joins.  In the simplified example below the join with the alias Delivery could have more than record and I want to bring back the record with a max value in a particular field in that table. </p>
  
  <pre><code>$inv_conditions = array(    'Invoice.invoice_date &gt;=' =&gt; $DateFormatter-&gt;dateForDB($dateFrom), 
                   'Invoice.invoice_date &lt;=' =&gt; $DateFormatter-&gt;dateForDB($dateTo),
                   'Invoice.id &gt;' =&gt; 385380 );    
  
  $join   =   array(array(
                  'table' =&gt; 'jobs',
                  'alias' =&gt; 'Jobs',
                  'type'  =&gt; 'LEFT',
                  'conditions'    =&gt; array('Invoice.job_id = Jobs.JOB_ID' ) 
                ),    
              array(
                  'table' =&gt; 'functional',
                  'alias' =&gt; 'Delivery',
                  'type'  =&gt; 'LEFT'
                  'conditions'=&gt; array('AND ' =&gt; array('Invoice.job_id = Delivery.JOB',
                                                       'Delivery.TYPE_STAGE = 1') 
                              )
                       )
  
          );    
  
  $invoices = $this-&gt;Invoice-&gt;find("all", array(
                  "fields"    =&gt;  array(
                              'Invoice.id',
                              'Invoice.job_id',
                              'Invoice.invoice_no',
                              'Invoice.consolidated_type',
                              'Invoice.customer_id_tbc',
                              'Invoice.invoice_date',
                              'Invoice.invoice_reference',
                              'Invoice.invoice_req',
                              'Jobs.PAYMENT_TYPE',
                              'Jobs.CUSTOMER',
                              'Jobs.MOST_RELEVANT_LINE',
                              'Delivery.DEPARTURE_DATE',
                              'Delivery.CNOR_CNEE_NAME',
                              'Delivery.TOWN_NAME',
                                ),
                  "conditions"    =&gt;  $inv_conditions,
                  "joins"     =&gt; $join
                  )
              );
  
  }
  </code></pre>
  
  <p>I can do this with SQL no problem as follows:</p>
  
  <pre><code>SELECT 
      jobs.JOB_ID, 
      jobs.CUSTOMER, 
      functional.JOB_LINE_ORDER, 
      functional.CNOR_CNEE_NAME, 
      functional.TOWN_NAME 
  FROM jobs JOIN functional ON
              jobs.JOB_ID = 'M201409180267'
          AND 
              functional.JOB =  jobs.JOB_ID
          AND 
              functional.TYPE_STAGE = 0 
          AND 
              functional.JOB_LINE_ORDER = 
                 (SELECT MAX(JOB_LINE_ORDER) FROM functional 
                  WHERE functional.JOB = 'M201409180267' AND functional.TYPE_STAGE = 0) 
  </code></pre>
  
  <p>I have tried using the following to the conditions array:</p>
  
  <pre><code>'conditions'    =&gt;    array('AND ' =&gt; 
                             array( 'Invoice.job_id = Delivery.JOB',
                                    'Delivery.TYPE_STAGE = 1'
                                    'Delivery.JOB_LINE_ORDER = MAXIMUM(Delivery.JOB_LINE_ORDER)' )
                              )
  </code></pre>
  
  <p>This does bring back results but not the correct ones and the resulting SQL generated by Cake does have a select in the where clause.  Is there a way of doing this when retrieving data in cake where the sql statement created will have a select in the where clause.  </p>
  
  <p>Any suggestions would be greatly appreciated.
  Thanks
  Bas</p>