CakePHP 2.1 find using contain with condition

Question

I have the following models.

1. Industry(id, name)
2. Movie(id, name, industry_id) [Industry has many movies]
3. Trailer(id, name, movie_id) [Movie has many trailers]

I need to find 6 latest trailers for each Industry. Every movie does not need to have a trailer or can have multiple[0-n].

The results must contain array of movies with atleast one trailer.

$this->Industry->find('all', array(
    'contain' => array(
        'Movie' => array(
            'Trailer' => array(
                'limit' => 1
            ),
            'order' => 'Movie.release DESC',
            'limit' => 6
        )
    ),
    'order' => 'Industry.name ASC'
));

Answer 1

Option 1 (best option IMO):

You were on the right track - all you need now is the addition of using CakePHP's CouterCache.

Basically, you set 'counterCache' => true in your $belongsTo Association, and add a field: singularModel_count (in your case, it would be trailer_count field in the movies table). Then, you can just condition against that field (see altered code below).

CakePHP's CounterCache automatically keeps track of how many items it has by adding/subtracting any time something is added/deleted via a CakePHP method.

$this->Industry->find('all', array(
    'contain' => array(
        'Movie' => array(
            'conditions' => array(
                'Movie.trailer_count >' => 0  // <-- Notice this addition
            ),
            'order' => 'Movie.release DESC',
            'limit' => 6,
            'Trailer' => array(
                'limit' => 1
            ),
        )
    ),
    'order' => 'Industry.name ASC'
));

Option 2:

The other option is to use JOINs (see CakePHP JOINs). When CakePHP queries each model called by "contain()", it actually does separate queries, then joins the data before returning to you. Because of that, you cannot limit the parent model based on conditions against the child model, because they're actually separate queries, and MySQL won't know what table you're trying to refer to in your conditions.

The downside to Option 2 is that it will make it difficult to do things like returning multiple trailers, since you'd be doing an INNER JOIN (likely) between Movie and Trailer.