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

Yii 1.1 General Discussion
1

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:


SELECT Challenge.id

FROM Challenge 

LEFT OUTER JOIN Result

ON Challenge.id = 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?


Challenge::model()->with('result')->findAll();

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!

2

How about this:


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

That was very straightforward!

Many thanks Mike.