Problem with Dynamic Relational Query Options

Archives Yii 1.0.x Discussions
1

Hi @all it seems I have a problem with setting Dynamic Relational Query Options.

In my case I have a model Activity with the relation




'members'=>array(self::MANY_MANY, 'Address', 'tbotheractivity(ActivityID, AddressListID)',

                'alias'=>'members',

                'order'=>'members.LastName ASC',

                'with'=>'company'

),

And when i am trying to set the sort order of this relation dynamically either by




$activity_model=Activity::model()->with(array('members'=>array('order'=>'members.LastName DESC')))->findbyPk($activity_id);

or via 

$activity_model=Activity::model()->findbyPk($activity_id);

$members=$activity_model->members(array('order'=>'members.LastName DESC'));

I get a sql query with this sort order




ORDER BY members.LastName ASC, members.LastName DESC

In this case the sort order of the relation wouldn’t be overwriten like mentioned here

http://www.yiiframework.com/doc/guide/database.arr#dynamic-relational-query-options

Or is something wrong with my code? I remember I have to set the alias for the many_many relation to be able to add sorting orders otherwise the columns wouldn’t be found which ends in a sql error.

Maybe there is a problem?

Regards Horizons

2

My first hunch is that you should try something like this:




$activity_model=Activity::model()->with('members')->findByPk($activity_id, array('order'=>'members.LastName DESC'));

Otherwise you can try defining a named scope for that relation and use it in your query chain. See http://www.yiiframework.com/doc/guide/database.ar#named-scopes for more on named scopes.

3

doesn’t work (generates sql error), also the sort order has to be set for the relation not the activty model itself.

And my code is the "equal" to the code from the documentation. So therefore its a problem either with the Many_Many Relation or something else.

I found that the function mergeWith($criteria) in class CBaseActiveRelation extends CComponent is called and creates the order in this part.




if(isset($criteria['order']) && $this->order!==$criteria['order'])

{

	if($this->order==='')

		$this->order=$criteria['order'];

	else if($criteria['order']!=='')

		$this->order.=', '.$criteria['order'];

}

And here I can see nothing of an "overwrite existing options as specified in the relations".

Edit:

In class CHasManyRelation extends CActiveRelation

function mergeWith($criteria) there is the comment

But there is only the code for limit, offset and index and not the order ?

If I just add




 if(isset($criteria['order']) && $criteria['order']!='') 

  $this->order=$criteria['order'];

I get what I want (ovewrite the sort order of the relation).

Is this a bug or wanted ??

4

still no reply?

Haven’t anyone else overruled a relational query sorting option ???

5

I had the same problem. I solved it simply by removing the order clause, in the model, in the definition of the relation. The order clause should only appear in the with() function, otherwise the order clause in the definition of the relation override it.

Change your relation like this:


'members'=>array(self::MANY_MANY, 'Address', 'tbotheractivity(ActivityID, AddressListID)',

                'alias'=>'members',

                'with'=>'company'

),

Maybe it’s a bug, but it is the only way I found to sort a relational attribute afterwards.

6

since its mentioned in the documentation it must be a bug.

http://www.yiiframework.com/doc/guide/database.arr#dynamic-relational-query-options

Good to know that i am not the only one. Guess I will open a bug ticket.