select, expressions and conditions in a relation

Archives Yii 1.0.x Discussions
1

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.

2

And any way to use a free form query to populate a relation could work instead, ideally without breaking AR relations.

Thanks for any thoughts on this. :slight_smile:

3

hi

What is target? a field in a table or a parameter

try this




      $criteria->select = 'IF(target=user1,user1,user2) AS user1, IF(target=user1,user2,user1) AS user2';

      $criteria->condition='target=user1 OR target=user2';

greetings

4

Hi Horacio. ^_^

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.

cheers

5

yes, you can

use alias of relation




$criteria->condition= 'table_name_alias.field=xxxx';

in the model




public function relations()

        {

         

                return array(

                        'rel_name' => array(self::...., 'Table_Name', 'id', 'alias'=>'table_name_alias'),

         

                );

        }

[edit]

mmmm, I think my answer is not what you want

sorry

You want to put the condition in the model?

see the Guide

6

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

	'Friendship(user1, user2)', 

	'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 appreciate the help regardless. :)

7

I might be missing something here, but are you only looking for a relation definition that would return the friend object? This seems somehow complicated for that.




'friends' => array(self::MANY_MANY, 'User',   // param hard-coded for clarity

        'Friendship(user1, user2)',

),

Shouldn’t this result already in a similar functionality?

now,




$friendList=User::model()->find(<condition>)->friends;

should be an array of related User models through the friends relation / Friendship table.

and,




$users=User::model()->findAll()->with('friends')->findAll();

should give you all users with related friend users populated into the objects already.

and,




$users=User::model()->findAll()->with(array(

   'friends'=>array('join'=>'RIGHT JOIN')

))->findAll();

should give you all users who have friends, with hte related friend objects populated.

Now, what am I missing in your question? :P

8

Thanks for the compact tutorial. ^_^

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.

Sorry I wasn’t clear earlier.

9

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.

10

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…