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.
Example:
/**
* @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.
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).
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)
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):
[sql]
SELECT COUNT(*) FROM messageThread as a, user as b
WHERE (a.userIdA = 60 AND a.userIdA = b.id) OR (a.userIdA <> 60 AND a.userIdB = b.id);
). 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).