Cdbcriteria: Finding All Records With Criteria For The Childrens Properties

I have a model boat, this model HAS MANY captains and HAS MANY buildLogs.

boat -+- HAS_MANY -> captains


      +- HAS_MANY -> buildLogs

I would like to find all boats without any captains and which has at least one buildLog of which the date property is within a specified range and the status property of the same buildLog is a certain value.

So a boat with one buildLog, where is within range and buildLog.status is the correct status should be fetched. But a boat with two buildLogs, of which one has the correct date and the other the correct status should not be fetched.

Can I create a CDbCriteria for this query? How can I create this CDbCriteria?

Hi i.amniels,

Probably something like this should work:

$criteria = new CDbCriteria();

$criteria->with = array(

    'buildLogs' => array(

        'joinType' => 'INNER JOIN',



$criteria->together = true;

$criteria->compare('', '>=' . $startDate);

$criteria->compare('', '<=' . $endDate);

$criteria->compare('buildLogs.status', $correctStatus);

$captainTable = Captain::model()->tableName;

$captainCountSql = "(select count(*) from $captainTable ct where ct.boat_id =";

$criteria->compare($captainCountSql, 0);

$boats = Boat::model()->findAll($criteria);

Not tested, though.