Using with() to find all records where a relation has not been set

Hi there,

I’m having difficulty finding the answer to my question as I’m struggling to know what to search for.

I want to find all records where a corresponding relation has not been set. More specifically, I have a Challenge model and a Result model and I want to find all of the Challenge records which don’t have a corresponding Result record.

Outside of Yii, I’ve written the following SQL which returns the correct resultset:


FROM Challenge 


ON = Result.challengeId

WHERE (Result.challengeId IS NULL);

How do I write this in the proper Yii format?

That is, what would the equivalent statement be to this?


Instead of returning all matches, it should return all Challenge records where there’s not a corresponding Result record.

Any advice would be much appreciated!

How about this:

Challenge::model()->with('result')->findAll('result.challengeId IS NULL');

That was very straightforward!

Many thanks Mike.