Problem with searching 2 foreign keys to one table

In my search method I have the following code …




$query->joinWith(["userFrom", "userTo"]);



This is 2 foreign keys that link to the same user table.

This generates the following error …

Syntax error or access violation: 1066 Not unique table/alias: ‘user’

Can anyone tell me how to get around this?

In your model you need to define two aliases for the same table.

Like for example:




    public function getUserFrom()

    {

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

                    ->from(User::tableName().' AS userFrom');

    }


    public function getUserTo()

    {

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

                    ->from(User::tableName().' AS userTo');

    }



Regards

Thanks, it works!

No Prob.

Glad I could help. ;)

The problem is I also have this code in a parent model …




public static function find() {

			

	return parent::find()->where([static::tableName() . ".deleted" => 0]);

			

}



Which now generates the following error because the tableName is no longer correct …

Column not found: 1054 Unknown column ‘user.deleted’ in ‘where clause’

Because the user.deleted is no longer user.deleted but userFrom.deleted etc.

Is there anyway around this?

Sorry, I have not overwritten default find() method so far…

Means: I don’t know yet.

When I have time I will try to reproduce and check later

Some Questions:

  • Where do you receive the error?

  • Only in your ModelSearch? Or everywhere?

  • Do you have deleted getUser() method in model?

  • If yes: what happens when you add it back additional to getUserFrom(), getUserTo()?

Regards

I only receive the error when the search code is used.

No I have no getUser method in model only getUserFrom and getUserTo.

If I put the method back in, the error still appears.

u have to define with table name.

like


tbl_city.cityId

What do you mean, where do I define with table name?

Anyone got any ideas for this one.

The first bit works with the aliases for the relations but because I have override the find() method like this …




public static function find() {

			


	return parent::find()->where([static::tableName() . ".deleted" => 0]);

							

}



But because now the tableName() will be different, because of the use of the alias it is throwing an error.