0

Using a Document class with PHPCR-ODM, it is possible to fetch results with the class repository, the results are automatically sorted by a field sort_order that is not in the Document class but in the database schema.

Example of a query logged in the Symfony Profiler:

SELECT path FROM phpcr_nodes WHERE parent = ? AND workspace_name = ? ORDER BY sort_order ASC

I have this simply query built with queryBuilder : 

$qb->from()
        ->document('AppBundle\Document\Product', 'product')
        ->end()
        ->where()
        ->neq()->field('product.type')->literal('category');
$query = $qb->getQuery();

The result is not sorted by the field sort_order like other queries, and I can't use the orderBy method as this is not a field of the Document class.

So, how can I sort my results?

loicb
  • 587
  • 2
  • 6
  • 24
  • Still confusing, read again "the results are automatically sorted by a field `sort_order` that is not in the Document class" then later "The result is not sorted by the field `sort_order` like other queries", do you want your query to sort by such `sort_order` column? In the other side I don't know how the rest of the queries are sorting "automatically" by `sort_order` because AFAIK that's not possible maybe I am wrong – ReynierPM Dec 20 '17 at 14:21

1 Answers1

0

Who said you can't use orderBy()? You should read the docs because certainly it's possible. Using your same code and assuming the sort column as sort_order see below:

$qb->from()
   ->document('AppBundle\Document\Product', 'product')
   ->end()
   ->where()
   ->neq()->field('product.type')->literal('category')
   ->orderBy()->desc()->field('product.sort_order');
ReynierPM
  • 17,594
  • 53
  • 193
  • 363
  • I can use `orderBy()`, but not with this field because it's not within my Document class. – loicb Dec 19 '17 at 15:22
  • Then how do you expect to sort by something which is not in your document? That does not make any sense to me!!! Is `sort_order` is coming from any other Document then just use `JOIN` in your QB to reach such column wherever it's and apply the same concept as shown on my answer – ReynierPM Dec 19 '17 at 15:25
  • When you use the `find()` method on the DocumentRepository class, hte results are sorted by this field. For example : `SELECT path FROM phpcr_nodes WHERE parent = ? AND workspace_name = ? ORDER BY sort_order ASC` – loicb Dec 19 '17 at 15:58
  • I am confused now, sorry but I am not following you, can you re-elaborate your question? – ReynierPM Dec 19 '17 at 16:23
  • I have updated my initial post, hope it is more clear now. Thank you. – loicb Dec 20 '17 at 14:12