I’m hoping I can transform this query on a User > Friendship > User, many_many relationship into a Yii relation:
[sql]SELECT IF(target=user1,user1,user2) AS user1, IF(target=user1,user2,user1) AS user2 FROM Friendship
WHERE target=user1 OR target=user2;[/sql]
This query (in mysql 5.0.x) selects all rows where ‘target’ appears as either user1 or user2, and always returns ‘target’ in the first column, with an alias of user1, even if it has to swap the column values using the IF statements in the select statement.
I don’t imagine this would work directly, since Yii has to do something under the hood with the manually supplied ‘select’ clauses to return the User records in question.
The above is a simplification, the actual self relation query is less comprehensible as it uses a composite key, but if I can get this worked out I should be able to handle that modification. I can supply those details if folks are interested.
Target is the parameter user id, an int. I’ll give the CDbCriteria suggestion a go and let you know what I find, thanks.
Is there a way to use CDbCriteria in a relation declaration? I’m playing with findBySql($thatQuery) as a start toward understanding what’s not working, but I’m hoping to put this in CActiveRecord::relations() format.
Thank you, the alias use isn’t one I’d read about.
I’ve been trying to get the relations entry set up, but have had no luck after putting the clause in an array and prefixing column names with ??.
Here’s an example of what I’ve tried, with the error it generated following it. I’ve tried several variations and am hoping someone can point out something I’ve missed.
'friends' => array(self::MANY_MANY, 'User', // param hard-coded for clarity
'select'=>array('IF(100 = ??.user1, ??.user1, ??.user2) AS user1, '),
'condition'=>'100 = ??.user1 OR 100 = ??.user2',
I think that should return the ID of the “friend”. I don’t know how the select should be structured so that it returns the whole User record, or if that’s even should be my goal in order to make the relation work. This is one variation of several that I’ve tried, and most get this error:
[html]Active record "User" is trying to select an invalid column "IF(100 = ??.user1, ??.user1, ??.user2) AS user1,". Note, the column must exist in the table or be an expression with alias[/html]
I didn’t really emphasize it in my first post, but when a user is searching for a “friend”, that friend can be listed in either column of the table. Due to other requirements, it would be a lot of fallout and time wasted if I tried to duplicate the data such that each user has a record where they are in the first column.
Technically I could have two of the usual sort of relations and check each of the columns separately, but there are potentially bad levels of overhead (can’t tell yet) and I’d like to avoid extra queries where possible. But I’ll need to do that if I can’t resolve this situation.
Well, it turns out that I just can’t do what I want using select and condition options in a relation. The condition option is AND’d with the automatically identified PK. Therefore one can’t change the basic PK definition, one can only restrict the results set further. There might be some tricky way to get the results I want by changing the join type, like using a cross join, but I don’t have time to explore that.
I was incorrect in the previous version of this reply, the below is expected behavior if you submit the select option as a simple string:
(I was wrong here) Upon further code investigation, it looks like [font=“Courier New”]CActiveFinder::getColumnSelect()[/font] is entirely stripping off the IF statement and just using the AS alias. The sql in mysql’s log has odd duplications as well. I’m going to bring this up in a more specific post and probably start a ticket on it.
I had a similar problem a while back, and ended up writing a custom PerspectiveBehavior for my Relation (or Friendship) model so that it could be accessed via $relation->me, $relation->other, etc. when necessary. I’m still not too happy about it but it works satisfactory for now…