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?
You are ordering by
location asc
, butlocation
is not defined in your query. You rename columnlocation
toLocation_location
, so you should order byLocation_location asc
.