Yii 2.0 table alias in SQL query

Question

I am using Yii2.0 and I have following error when I doing filtering with relationship:

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='[email protected]'

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.

Is there a way to have the alias in the query?

Answer 1

Use method "alias('string')".

User::find()->alias('u')->joinWith(['userContacts' => function($query) use ($email){
  $query->alias('uc')->where(['uc.email' => $email])
 }])
->one();

Look this API doc

Answer 2

In MySQL, your user table is called tbl_user_contact. However, you are referring to it as userContact, which results in the error.

When adding conditions, you should refer to fields using the actual table name. Here's the proper code:

User::find()->joinWith('userContacts', false)
    ->where([UserContact::tableName().'.email' => $email])
    ->one();

You could just replace UserContact::tableName().'.email' with tbl_user_contact.email, but using tableName() is better practice.

Answer 3

ActiveQuery extends of Query, you can use methods of query in ActiveQuery:

    $query = \app\models\db\AnuncioConsulta::find();
    $query->from(\app\models\db\AnuncioConsulta::tableName() . ' as ac' );

    $query->join = [
       ['INNER JOIN', 'anuncio as a'  , ' a.id = ac.anuncio_id AND a.status = 1 '],
       ['INNER JOIN', 'autor as au'   , ' au.id = a.autor_id AND au.agente_inmobiliario = 0 '],
       ['INNER JOIN', 'usuario as u'  , ' u.id = au.object_id '],
    ];

    $query->andWhere('ac.news      =:status' , [':status' =>  1 ]);
    $query->andWhere('ac.delete    =:status' , [':status' =>  0 ]);
    $query->andWhere('u.id         =:userId' , [':userId' => Yii::$app->user->id ]);

    return new \yii\data\ActiveDataProvider([
        'query' => $query,
    ]);