Sort Order for getCollectionGraph

183 Posts

mikrobi Reply #1, 15 years, 8 months ago

Hi!

I’m trying to receive a collection of categories which contain several products in one array. I’d like to sort the products of each category by their name with the following code:

<?php
$obecjtGraph = '{ "Products":{} }';
$criteria = $modx->newQuery('Category');
$criteria->sortby('Product.name');
$categories = $modx->getCollectionGraph('Category', $obecjtGraph, $criteria);

But this doesn’t work. Sorting by a field of a category does work:

<?php
$obecjtGraph = '{ "Products":{} }';
$criteria = $modx->newQuery('Category');
$criteria->sortby('Category.name');
$categories = $modx->getCollectionGraph('Category', $obecjtGraph, $criteria);

Here is my (simplified) schema definition:

<?xml version="1.0" encoding="UTF-8"?>
<model package="products" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" version="1.1">
	<object class="Product" table="product" extends="xPDOSimpleObject">
		<field key="id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" generated="native" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" index="unique" />
		<field key="category" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" />

		<aggregate alias="Category" class="Category" local="category" foreign="id" cardinality="one" owner="foreign" />
	</object>
	<object class="Category" table="category" extends="xPDOSimpleObject">
		<field key="id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" index="pk" generated="native" />
		<field key="name" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		
		<composite alias="Products" class="Product" local="id" foreign="category" cardinality="many" owner="local" />
	</object>
</model>

Any hints how I can sort the collection graph by the products’ name?

Add-On to easily manage your multilingual sites: Babel

- 183 Posts
mikrobi Reply #2, 15 years, 8 months ago
Ok, now I got it to work. After debugging the generated SQL I realized that xPDO adds an "ORDER BY `Category`.`id` ASC" by default to the query.

This creates the desired output:
```
<?php
$obecjtGraph = '{ "Products":{} }';
$criteria = $modx->newQuery('Category');
$criteria->sortby('`Products`.`product_number`');
$categories = $modx->getCollectionGraph('Category', $obecjtGraph, $criteria);
```
Add-On to easily manage your multilingual sites: Babel