Using Mysql Function In Relation

Is it possible to use a MySQL function in defining a relation? I want to make a join with my user table for a message system, where the field that needs to be joined is depending on which user is logged in.



      * @return \yii\db\ActiveRelation


    public function getPartner()


	return $this->hasOne(User::className(), ['id' => 'IF(userIdA = 60, userIdB, userIdA)']);


But this results in the following query (which throws an error):

SELECT COUNT(*) FROM `messageThread` INNER JOIN `user` ON `messageThread`.[[IF(userIdA = 3, userIdB, userIdA)]] = `user`.`id`.....

It wraps [[ … ]] around the MySQL IF() function.

I already tried to put the IF() function inside \yii\db\Expression, but this gives the same error.

Seems like it’s time to rethink your DB design.

I’m sure my database design is ok (it’s not the first database i’m designing ;)). It’s a messaging system where two people can send messages to each other. The messages (MessageThread) (incoming and sent messages) should be displayed in a inbox. So I always need two fields for the user, the sender and the receiver, but you don’t know if the currently logged in user is the sender or the receiver for one row in MessageThread, so i need to check that with the IF() function to get the MessageThread partner (the sender if the currently logged in user is the receiver or the receiver if the currently logged in user is the sender).












Any idea? I think it’s good you can use MySQL functions anyway in your relations (using \yii\db\Expression for example).

I think this is related to ActiveRelation On Condition

Conditional JOIN depending on current user ID sounds weird to me.

You can solve this problem at php-side (<?= $message-> fromUserId == \Yii::$app->user->id ? ‘author’ : ‘not author’ ?>).

No, srsly, think twice before doing such a thing.

Yes it might seems weird, but it’s the only solution i can think of.

I will try it in PHP. For your solution, i have to retreive the partner data (i also want to get data from the ‘partner’) for each row of MessageThread, which seems not very efficient. (it’s about the MessageThread table btw, not Message)


I ended up writing my own $query->innerJoin(…);

One extra join is not a big deal, compared to inability of (for example) effectively caching your query.

I know, but in your solution i should perform a query for every MessageThread row. In my solution i’m using a join (a conditional one).

Better db/app design maybe can help… but if you still are keen on this, you could have an alternative query without IF statement something like below (not sure then if you can use the OR conditions in your relations):


SELECT COUNT(*) FROM messageThread as a, user as b

WHERE (a.userIdA = 60 AND a.userIdA = OR (a.userIdA <> 60 AND a.userIdB =;