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 buildLog.date 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('buildLogs.date', '>=' . $startDate);

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

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


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

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

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


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



Not tested, though.