CODEWITHSUNDEEP
yii2
Uitrix
Uitrix

Reputation: 21

Yii2 is there a way to specify tablename in ActiveQuery conditions (like andWhere) in a nice and short way

I make a query (with \yii\db\ActiveQuery) with joins, and some fields in "where" clause become ambigous. Is there a nice and short way to specify the name of the current model`s (ActiveRecord) table (from which one the ActiveQuery was instantiated) before the column name? So I can use this all the time in all cases and to make it short.

Don't like doing smth like this all the time (especially in places where there're no joins, but just to be able to use those methods with joins if it will be needed):

// in the ActiveQuery method initialized from the model with tableName "company"
$this->andWhere(['{{%company}}.`company_id`' => $id]);

To make the "named scopes" to work for some cases with joins..

Also, what does the [[..]] mean in this case, like:

$this->andWhere(['[[company_id]]' => $id]);

Doesn't seem to work like to solve the problem described above.

Thx in advance!

P.S. sorry, don't have enough reputation to create tag yii2-active-query

Upvotes: 2

Views: 1205

Answers (2)

mojtaba khani
mojtaba khani

Reputation: 11

to get real table name :

Class :

ModelName::getTableSchema()->fullName

Object :

$model::getTableSchema()->fullName

Upvotes: 1

PLM57
PLM57

Reputation: 1296

Your problem is a very common one and happens most often with fields liek description, notes and the like.

Solution

Instead of 

$this->andWhere(['description'=>$desc]);

you simply write 

$this->andWhere(['mytable.description'=>$desc]);

Done! Simply add the table name in front of the field. Both the table name and the field name will be automatically quoted when the raw SQL is created.

Pitfall

The above example solves your problem within query classes. One I struggled over and took me quite some time to solve was a models relations! If you join in other tables during your queries (more than just one) you could also run into this problem because your relation-methods within the model are not qualified.

Example: If you have three tables: student, class, and teacher. Student and teacher probably are in relation with class and both have a FK-field class_id. Now if you go from student via class to teacher ($student->class->teacher). You also get the ambigous-error. The problem here is that you should also qualify your relation definitions within the models!

public function getTeacher()
{
    return $this->hasOne(Teacher::className(), ['teacher.id' => 'class.teacher_id']);
}

Proposal

When developing your models and query-classes always fully qualify the fields. You will never ever run into this problem again...that was my experience at least! I actually created my own model-gii-template. So this gets solved automatically now ;)

Hope it helped!

Upvotes: 0

Related Questions