CODEWITHSUNDEEP
yii2
Ripper
Ripper

Reputation: 1162

How to specify value in model relation keys?

I have tables user and profile, one user has max one profile, and is specified by user_id and table name in profile. I do not use foreign keys there.

The reason I do it this way, is because I have other tables like company which also uses table profile, so reference is specified by relation_id = primary key of related table and relation = table name

profile 
    relation_id
    relation

What I want to achieve is to set model relation to be equal to string user, so not to use key there, but to use value instead.

User.php

public function getProfile()
{
    return $this->hasOne(Profile::className(),
        ['relation_id' => 'user_id', 'relation' => User::tableName()]);
}

Error I get:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user.user' in 'on clause'
The SQL being executed was:

SELECT COUNT(*) FROM `user_login`
LEFT JOIN `user` ON `user_login`.`user_id` = `user`.`user_id` 
LEFT JOIN `profile` ON `user`.`user_id` = `profile`.`relation_id`
    AND `user`.`user` = `profile`.`relation`

It is for generating GridView so sql fails on count first, but error would be the same for select *

SQL I want to achieve:

SELECT * FROM `user_login`
LEFT JOIN `user` ON `user_login`.`user_id` = `user`.`user_id` 
LEFT JOIN `profile` ON `user`.`user_id` = `profile`.`relation_id`
    AND `profile`.`relation` = 'user'

So the question is, How to specify value in model relation keys?

Upvotes: 1

Views: 152

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133400

if your User has a relation hasOne with profile you should use only 

public function getProfile()
{
   return $this->hasOne(Profile::className(),
    ['relation_id' => 'user_id']);
}

and if you need a on condition use 

  public function getProfile()
  {
     return $this->hasOne(Profile::className(),
      ['relation_id' => 'user_id'])->andOnCondition(['relation' => User::tableName()]);

  }

Upvotes: 2

Related Questions