CakePHP Filtering Based on Associated Data with Pagination

Question

Here is my scenario:

• I have two tables: subfirms and subfirmdetails.
• I am trying to paginate the subfirms but with filters depending on certain subfirmdetail values, such as AUM (total money at a firm).

Here is my pagination code:

$this->paginate['Subfirm'] = array(
            'contain' => array(
                'Firm' => array(
                    'Firmtype',
                    'conditions' => $firmtype,
                    'Job' => array(
                        'Person'
                    ),
                    'Attachment' => array(
                        'conditions' => array(
                            'Attachment.attachmentgroup_id' => 7
                        ),
                    ),
                ),
                'Subfirmdetail' => array(
                    'Subfirmdetailtype' => array(
                        'Subfirmdetailoption',
                    ),
                ),
                'Substrategy'
            ),
            'order' => array(
                $order,
            ),
            'conditions' => array(
                'Subfirmdetail.value = 40',
            ),
        );

        return $this->paginate('Subfirm');

Let's say I want the Subfirmdetail.value to be 40, otherwise don't show the firm. This is the type of scenario I am trying to filter based on (or even deeper...). Been stuck for a while so any help would be appreciated!

Answer 1

You need to use INNER Joins.

 $joins = array(
   array(
       'table' => 'subfirmdetail',
       'alias' => 'Subfirmdetail',
       'type' => 'inner',
       'conditions' => array( 'Subfirmdetail.value = 40', 'Subfirmdetail.subfirm_id = Subfirm.id' )
       ),
   array(
       'table' => 'subfirmdetailtype',
       'alias' => 'Subfirmdetailtype',
       'type' => 'inner',
       'conditions' => array( 'Subfirmdetailtype.subfirmdetail_id = Subfirmdetail.id' )
       ),
   array(
       'table' => 'subfirmdetailoption',
       'alias' => 'Subfirmdetailoption',
       'type' => 'inner',
       'conditions' => array( 'Subfirmdetailoption.subfirmdetailtype_id = Subfirmdetailtype.id' )
       )
   );




  $this->paginate['Subfirm'] = array(
        'fields' => array(
            'Subfirmdetail.*', 'Subfirmdetailtype.*', 'Subfirmdetailoption.*'
            ),
        'joins' => $joins,
        'contain' => array(
            'Firm' => array(
                'Firmtype',
                'conditions' => $firmtype,
                'Job' => array(
                    'Person'
                ),
                'Attachment' => array(
                    'conditions' => array(
                        'Attachment.attachmentgroup_id' => 7
                    ),
                ),
            ),
            'Substrategy'
        ),
        'order' => array(
            $order
        )
    );

    return $this->paginate('Subfirm');