Model joinWith column not found problem

Yii 2.0 General Discussions
1

Hi,

I have following error:

Exception (Database Exception) ‘yii\db\Exception’ with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘userContact.email’ in ‘where clause’

The SQL being executed was: SELECT tbl_user.* FROM tbl_user LEFT JOIN tbl_user_contact ON tbl_user.id = tbl_user_contact.user_id WHERE userContact.email=‘me@me.com’’

And it is obvious that the table name alias is not given. Following is my code that generate the query above:

Class Files

class User extends ActiveRecord{

public function getUserContacts(){


    return $this->hasMany(UserContact::className(), ['user_id' => 'id']);


}

}

class UserContact extends ActiveRecord {

public function getUser(){


    return $this->hasOne(User::className(), ['id' => 'user_id']);


}

}

Query

User::find()->joinWith(‘userContacts’, false)

                ->where(['userContact.email' => $email])


                ->one();

I follow the instruction given here: http://www.yiiframework.com/wiki/780/drills-search-by-a-has_many-relation-in-yii-2-0/#hh4

Can somebody tell me what might be wrong?

2

Hi,

You can try something like :




$userTablename=UserContact::tableName();


User::find()->joinWith('userContacts', false)

	->where([  "$userTablename.email" => $email])

	->one();

B)

3

Thank you for your answer!

What if i need to have two relations using the same table? Is there a way to give alias to the table? In Yii1.0, i used to use the relationship name to handle that.

4

I’ve never used table alias but from the doc I can see that it’s possible using a simple syntax. Check this out.

ciao

B)