Results are not sorted with query builder

[lowki]

I make a query with the query builder.

$qb->from()
      ->document('MainBundle\Document\Product', 'product');
$qb->where()
      ->child('/cms/simple/products/fauteuil-de-bureau', 'product');

The MySQL query executed is this one

SELECT path AS arraykey, id, path, parent, local_name, namespace, workspace_name, identifier, type, props, depth, sort_order
FROM phpcr_nodes WHERE workspace_name = "default" AND ((path LIKE "/cms/simple/products/fauteuil-de-bureau/premium/%" OR path = "/cms/simple/products/fauteuil-de-bureau/premium") AND depth <= ((SELECT depth FROM phpcr_nodes WHERE path = "/cms/simple/products/fauteuil-de-bureau/premium" AND workspace_name = "default") + 0) OR (path LIKE "/cms/simple/products/fauteuil-de-bureau/executive/%" OR path = "/cms/simple/products/fauteuil-de-bureau/executive") AND depth <= ((SELECT depth FROM phpcr_nodes WHERE path = "/cms/simple/products/fauteuil-de-bureau/executive" AND workspace_name = "default") + 0) OR (path LIKE "/cms/simple/products/fauteuil-de-bureau/casual/%" OR path = "/cms/simple/products/fauteuil-de-bureau/casual") AND depth <= ((SELECT depth FROM phpcr_nodes WHERE path = "/cms/simple/products/fauteuil-de-bureau/casual" AND workspace_name = "default") + 0)) ORDER BY sort_order ASC;

Which leads to this results :

+-------+---------------------------------------------------+----------------+--------------------------------------+------------+
| id    | path                                              | workspace_name | identifier                           | sort_order |
+-------+---------------------------------------------------+----------------+--------------------------------------+------------+
| 12225 | /cms/simple/products/fauteuil-de-bureau/executive | default        | d2db4f27-c828-412f-b74f-3850f6335580 |          0 |
| 12224 | /cms/simple/products/fauteuil-de-bureau/premium   | default        | d6aa3a34-bfd4-4d32-afd0-14607e220b2c |          1 |
| 12226 | /cms/simple/products/fauteuil-de-bureau/casual    | default        | 3b833ad1-3eee-4929-841d-5195166830a1 |          2 |
+-------+---------------------------------------------------+----------------+--------------------------------------+------------+

But the results returned by the query builder are not sorted by the sort_order field.

[dbu]

the sort order is only used when accessing nodes / documents in tree navigation. for queries, there is no defined sort order unless you use a order by. (but afaik there is no order by child-order).

the preferred mode of interacting with phpcr is by using the tree structures. doing a query like in your example is porting the SQL patterns to phpcr. in a relational database, this is how you have to do it. but with phpcr, you can have a document Category and then do something like:

$category = $dm->find('/cms/simple/products/fauteuil-de-bureau');
foreach ($category->getProducts() as $product) {...

getProducts can use the @Children mapping to map all children. if you have a mixed tree, getProducts can do instanceof checks to filter out non-product children.

[lowki]

I see, but is there a way to get the sort_order value in the results?
And why the results order doesn't reflect the mysql query? (Just for my understanding)

[dbu]

the order is only meaningful within the children of a specific node. query are usually on whole ranges and not children of a single parent. if i have a tree like this

               A
            /    \  
          B        C
        /   \     /  \
      D      E   F    G

and my query finds B and F, the "sort order" has no meaning. sort order is relevant for B, C and for D, E and for F, G but as soon as you leave the context of a single parent, the sort order does not mean anything.

[lowki]

I understand, thank you for your help.