Yii2 and ActiveRecord how to get relations in a query

Question

I am new to Yii2 and I need to do this:

I have two tables: Table: tbl_user Fields: user_id, last_company_id

Data: user_id = 29 last_company_id = 49

Table: tbl_user_subscriber_company: Fields: user_id, company_id, current_module

Data: user_id = 29, company_id = 49, current_module = 'Module A'

user_id = 29,
company_id = 50,
current_module = 'Module B'

I need to get the current_module for the user and last_company_id by calling the User model. (A requirement of the company I work for, they build a user object from the user model with all the fields relating to it from the database)

So I want to translate this mysql query:

select current_module
from tbl_user a
RIGHT JOIN tbl_user_subscriber_company b
ON a.user_id = b.user_id
AND a.last_company_id = b.company_id
where a.last_company_id = 49
and a.user_id = 29

Into yii2 ActiveRecord in the Usel model.

I need to have a function called: getCurrentmodule that will return the user's current module for the selected user and his last_company_id.

Hope I make sense, a bit hard for me to explain.

Refer: http://stackoverflow.com/questions/29447538/how-to-use-join-in-yii2-active-record-for-relational-model — Insane Skull, Apr 05 '16 at 11:52

score 1 · Answer 1 · answered Apr 05 '16 at 12:12

I am not sure but you can try something like,

For example:

User::find()
     ->joinwith('tbl_user_subscriber_company',true,'RIGHT JOIN')
     ->select(['current_module'])
     ->where(['user_id' => tbl_user_subscriber_company.user_id])
     ->andWhere(['last_company_id' => tbl_user_subscriber_company.company_id])
     ->andWhere(['last_company_id' => 49])
     ->andWhere(['user_id' => 29])
     ->all();

OR

$data = (new \yii\db\Query())
        ->select('current_module')
        ->from('tbl_user user')
        ->rightJoin('tbl_user_subscriber_company usc','user.user_id = user_id')
        ->where('user.last_company_id = usc.company_id')
        ->andWhere('user.last_company_id = 49')
        ->andWhere('user.user_id = 29')
        ->all();

Thank you @Insane Skull. I convinced my employers I need time to learn yii before I can work with it. Will try your code soon and will let you know. — Sigal Zahavi, Apr 06 '16 at 05:49

Yii2 and ActiveRecord how to get relations in a query

1 Answers1