Using OR operator in join

Yii 1.1 General Discussion
1

How can i use the OR operator in the ON clause when creating a JOIN?




public function relations()

{

    // NOTE: you may need to adjust the relation name and the related

    // class name for the relations automatically generated below.


    return array(

        'users' => array(self::HAS_MANY, 'User', 'accountId'),

        'fields' => array(self::HAS_MANY, 'Field', 'accountId', 'on' => ' [b]OR[/b] accountId IS NULL'),

    );

}

I want to fetch all fields with accountId related to this model instance PLUS those with ‘accountId IS NULL’

I took a look at the code and saw that the AND operator is hardcoded (CActiveFinder.php on line 1222). =/

2

Try this (not tested though)




  'fields' => array(self::HAS_MANY, 'Field', '', 'on' => 't.id = fields.accountId  OR fields.accountId IS NULL'),

http://www.yiiframework.com/doc/api/1.1/CActiveRelation#on-detail

/Tommy

3

did not work. :(

[SQL]

SELECT fields.id AS t1_c0, fields.accountId AS t1_c1, fields.name AS t1_c2, fields.jsonPath AS t1_c3, fields.type AS t1_c4

FROM field fields

WHERE (id = fields.accountId OR fields.accountId IS NULL) AND (fields.accountId=:ypl0).

Bound with :ypl0=‘4’

[/SQL]

This AND operator that is the problem =/

4

Did you remove the FK array element from the relationship definition?

/Tommy

5

You mean, do something like this:




public function relations()

{

    // NOTE: you may need to adjust the relation name and the related

    // class name for the relations automatically generated below.


    return array(

        'users' => array(self::HAS_MANY, 'User', 'accountId'),

        'labels' => array(self::HAS_MANY, 'Label', 'accountId'),

        'fields' => array(self::HAS_MANY, 'Field', '', 'on' => 't.id = fields.accountId OR fields.accountId IS NULL'),

    );

}

This code give me follow error:




CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.id' in 'where clause'

If I just remove the third element of the relation definition, it throw me another error.




Active record "Account" has an invalid configuration for relation "fields". It must specify the relation type, the related active record class and the foreign key.

Any other idea?

6

What query did you use in your previous post?

/Tommy

7

You wanna know what query shown me that error?

If yes, the generated SQL was:

[SQL]

SELECT fields.id AS t1_c0, fields.accountId AS t1_c1, fields.name AS t1_c2, fields.jsonPath AS t1_c3, fields.type AS t1_c4

FROM field fields

WHERE (t.id = fields.accountId OR fields.accountId IS NULL)

[/SQL]

8

No, I want to see the PHP statements.

Did you start with Gii generated code?

Which model is primary in the query?

Which model defines the relationship we’re talking about?

Examples




$crit = new CDbCriteria;

$crit->with = 'fields';

$crit->together = true;

$dataprovider = CActiveDataProvider('Primary', $crit);

// or

$models = Primary::model()->with('fields')->findAll();

The SQL you posted is probably not the right one. It uses field as primary table (which is unexpected here), does have a WHERE clause (condition in Yii), but no JOIN with a field table (as specified in the fields relation we’re talking about).

First of all you should probably read this section of the Definitive Guide to Yii.

Edit: I may be you posted the correct SQL assuming lazy loading (I never examined the generated SQL from a lazy load operation). My examples above use eager loading.

/Tommy

9

Ok, sorry. Let me explain further, and apologize in advance for any mistakes in my english.

I have accounts and each account has fields. However there are some fields that are common to all accounts, so the column "accountid" is null for these fields.

So when I want to fetch the fields for an account, I need to fetch all the fields where the column "accountid"" equals the primary key of the account plus those that are null.

So I created a model called "account" and defined the following relation:

‘fields’ => array(self::HAS_MANY, ‘Field’, ‘accountId’),

However, when I do "Account::model()->findByPk(1)->fields" the fields whose "accountid" are null are not returned.

So I tried to put some clauses using the ‘on’ and ‘condition’ in the definition of the relation, but without success, because I need an ‘OR’ operator, not an ‘AND’.

Clear now? :D

10

To me it doesn’t make sense to try accessing unrelated Field records from Account. After getting some sleep I changed my mind.

See my next post.

I think I would do it the other way around. For a start try this: This is a another possible solution:

1. In Field.php, define a relationship of type BELONGS_TO




'account' => array(self::BELONGS_TO, 'Account', 'accountId'),

2. Use eager loading and do a simple test like this




$models = Field::model()->with('account')->findAll('accountId = 1 OR accountId IS NULL');

3. Access the query result




foreach ($models as $model)

{

  echo $model->id . ' ' . $model->accountId . ' ';

  echo isset($model->account) ? $model->account->something : '';

  echo '<br/>';

}

(not tested)

BTW you should not need eager loading of related records in this latter case (the with part).

/Tommy

11

My first answer should work with eager loading. You didn’t show the source that generates the actual query, but at least I can advice you to add with and together, for the table alias to work.

/Tommy