-1

I'm trying to figure out what is wrong with the sort order on an xPDO SQL query,

This query

$criteria = $this->modx->newQuery($table);
$criteria->sortby($sortby,$sortdir);
$options = $this->modx->getCollectionGraph($table,$criteria);
$criteria->prepare();
echo '<pre>'.$criteria->toSQL().'</pre>';

results in this SQL ~ which is correct:

SELECT `Location`.`id` AS `Location_id`, `Location`.`created` AS `Location_created`,
 `Location`.`modified` AS `Location_modified`, `Location`.`location` AS 
`Location_location`, `Location`.`group` AS `Location_group`, `Location`.`comment` 
AS `Location_comment` 
FROM `flow_location` AS `Location` ORDER BY location asc

however, if I try to loop over the query:

foreach($options as $option) {
    echo $option->get($value).'<br>';
}

it will display the records by the order id in the database!?

how do I fix that?

Sean Kimball
  • 4,506
  • 9
  • 42
  • 73

1 Answers1

1

You are ordering by location asc, but location is not defined in your query. You rename column location to Location_location, so you should order by Location_location asc.

SELECT `Location`.`id` AS `Location_id`, `Location`.`created` AS `Location_created`,
 `Location`.`modified` AS `Location_modified`, `Location`.`location` AS 
`Location_location`, `Location`.`group` AS `Location_group`, `Location`.`comment` 
AS `Location_comment` 
FROM `flow_location` AS `Location` ORDER BY location asc
Lee Salminen
  • 900
  • 8
  • 18
  • If I run that query in an sql browser it does work & order correctly - I see what you are saying trying to order by Location_location yeilds the same result [$criteria->sortby($table.'_'.$sortby,$sortdir); I'm not sure you can sort getCollectionGraph, because the code does work if I just use 'getCollection' – Sean Kimball Mar 25 '14 at 02:44
  • Gotcha. Dont know much about modx, but you could sort the object after the query in php with a usort() function. Seach SO for some examples – Lee Salminen Mar 25 '14 at 03:27