CakePHP: Retrieving records based on field in related model

Question

I'm trying to search based on a field in a related model. I can do so with the belongsTo model, but not the hasMany model. I'm sure this is something simple I'm overlooking but I can't see it. Could someone point me in the right direction (or even the cakebook page I need - searched, but didn't find anything that looked like this). Thanks

In groups controller: (this doesn't work)

$group = $this->Group->find('all', array('conditions' => array('Voucher.id' => '55')));

This does:

$group = $this->Group->find('all', array('conditions' => array('TourOperator.id' => '3')));

Background file snippets:

Group model:

var $belongsTo = array(
        'TourOperator' => array(
            'className' => 'TourOperator',
            'foreignKey' => 'tour_operator_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );

    var $hasMany = array(
        'Voucher' => array(
            'className' => 'Voucher',
            'foreignKey' => 'group_id',
            'dependent' => true,
            'conditions' => '',
            'fields' => '',
            'order' => 'Voucher.date, Voucher.meal_type_id'         
        )
    );

Voucher model:

var $belongsTo = array(
        'Group' => array(
            'className' => 'Group',
            'foreignKey' => 'group_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );

Tour Operator model:

var $hasMany = array(
    'Group' => array(
        'className' => 'Group',
        'foreignKey' => 'tour_operator_id',
        'dependent' => false,
        'conditions' => '',
        'fields' => '',
        'order' => '',
        'limit' => '',
        'offset' => '',
        'exclusive' => '',
        'finderQuery' => '',
        'counterQuery' => ''
    )
);

Update (as posted in comment below, but clearer to read here)

I've ended up using this.

$groups = $this->Group->Voucher->find('all', array('fields' => 'DISTINCT group_id', 'conditions' => array('Voucher.status' => 'pending')));
$group_ids = Set::extract($groups, '/Voucher/group_id');
$data = $this->Group->find('all', array('conditions' => array('Group.id' => $group_ids)));

I get a distinct list of group IDs matching my criteria, create an array of just the IDs and then use that to pull the groups so that the arrays are ordered as I expect them in my view.

Answer 1

With CakePHP's Containable behaviour you can't use conditions on related data in hasMany and hasAndBelongsToMany associations. This is because it uses seperate queries to get those associations.

With the Linkable behaviour, however, you can use data from hasMany associations to filter records. The behaviour can be downloaded here: https://github.com/Terr/linkable

Linkable uses standard JOINs to, well, join related data. This means it doesn't pull in all the related data (ie, all the Vouchers related to your Group(s)), just like a normal SELECT. However, it can work together with Containable to do both.

You can find examples of how Linkable works in the README.

(disclaimer: I currently maintain Linkable, but that is not the reason I point to it)

Answer 2

Can you recast your query to $this->Voucher->find, and then use the associated Group data returned from that query? As you say, there's no problem finding groups by their TourOperator id, and the Groups/Voucher relationship looks like it's the inverse of that.