Scope condition in join clause ?

Yii 1.1 General Discussion
1

Hi all,

I’m having something like




Packages::model()->currentPackages()->with('bills:notIssued')->find();

The scope "notIssued" is




'notIssued'=>array(

   'condition'=>self::getTableAlias().'.issued="0"',

),

the query that is executed places the condition in the join clause and not in the WHERE clause.

how can i force it to be in the WHERE clause ?

2

You should add condition to Packages.

3

you mean




Packages::model()->currentPackages()->with('bills')->find(array('condition'=>'bills.issued="0"'));

this does the trick, but why have the scopes then ?

i really wanted to define it in the model and then use it everywhere without having to change the code when the definition of the scope changes

4

btw., are you using 1.1.3 or SVN?

5

The ON clause is the right place for conditions of joined tables. You could use an INNER JOIN instead if you want your old behavior.

Also see here:

http://www.yiiframework.com/forum/index.php?/topic/10185-using-relations-and-conditions/

6

I’m using 1.1.3

Mike, how will INNER JOIN help me out ? Can you give me an example ?

Is there a way to force the condition to be in the WHERE part ?

Or my only solution is to add a condition in the find method ?

7

You can change the joinType property in your relations() declaration of "bills".

INNER JOIN will filter out all those rows from the resultset where the JOINed table would have NULL values. That should give you the same result as if your condition would be in the WHERE clause.

8

Thanks Mike ! That was excellent.

Now my scope is:




'notIssued'=>array(

   'joinType'=>'INNER JOIN',

   'condition'=>self::getTableAlias().'.issued="0"',

),

Wouldn’t be better to have ‘condition’ applied to the WHERE and ‘on’ applied to the JOIN ?

9

Not when you’re doing a relational query. You don’t always want the condition of your related tables to affect the result of your main table. But that’s what you do, if you put it into WHERE. Now you can use the joinType to select between the two options.

INNER JOIN: Only leave those main records in the result that have at least 1 related record

LEFT JOIN: Leave all main records in the result

10

Maybe you’re right :)

anyway, thank you very much for your help