Multi-column sorting using relations?

Archives Yii 1.0.x Discussions
1

I'm sorry if this has been asked before, but I couldn't find anything in my searches…

In my 'Locations' listing, I would like to sort by 'System' first, and the name of the location second, but I don't want to sort by system ID… I want to search by name…

I tried making an alias for the relation with:

<?php


	public function relations()


	{


		return array(


            'system' => array(self::BELONGS_TO, 'System', 'systemId', 'alias'=>'system'),


            'stores' => array(self::MANY_MANY, 'Store', 'LocationStore(locationId, storeId)')


		);


	}


?>

And then used the following query to get the sorted version:

<?php


	$criteria=new CDbCriteria;


	$criteria->order = 'system.name ASC, name ASC';


?>

But running it causes the complaint that 'system.name' is an unknown clause… I was looking at CSort as that one can solve by relation columns… However, I couldn't quite see a way to make it sort by multiple columns…

2

How are you doing the query? Could you enable logging and see what is the SQL?

3

I'm executing the query like this:

<?php


$locationList=Location::model()->findAll($criteria);


?>

I also tried the following:

<?php


$locationList=Location::model()->with('system')->findAll($criteria);


?>

And the query it produces is:

[trace] [system.db.CDbCommand] query with SQL: SELECT COUNT(*) FROM `Location` ORDER BY system.name ASC, name ASC
4

The problem occurs when you are doing count(). You don't need 'order' option with count().

5

Okay, so I've moved down the change of criteria a bit… So I moved it down, but then it complained about the 'name' column being ambiguous. I assumed this meant I had to change it to:

<?php


$criteria->order = 'system.name ASC, ??.name ASC';


?>

However this results in the following error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '??.name ASC LIMIT 10' at line 1

The query:

query with SQL: SELECT `Location`.`id` AS t0_c0, `Location`.`systemid` AS t0_c1, `Location`.`name` AS t0_c2, `Location`.`lawlevel` AS t0_c3, `Location`.`techlevel` AS t0_c4, `Location`.`resource` AS t0_c5, `Location`.`bank` AS t0_c6, `Location`.`repair` AS t0_c7, `Location`.`manufacture` AS t0_c8, `Location`.`storage` AS t0_c9, `Location`.`cmarket` AS t0_c10, `Location`.`mall` AS t0_c11, `Location`.`factory` AS t0_c12, `Location`.`refine` AS t0_c13, `Location`.`rarerefine` AS t0_c14, `Location`.`npc` AS t0_c15, system.`id` AS t1_c0, system.`name` AS t1_c1 FROM `Location`  LEFT OUTER JOIN `System` system ON `Location`.`systemId`=system.`id` ORDER BY system.name ASC, ??.name ASC LIMIT 10
6

You need to use prefix 'Location'.

7

facepalm

Woah… I feel stupid now. Thank you.