Symfony2 query builder adding custom order to query

Question

I am using QueryBuilder to get the query of all the products with all joins in my project.. Then I use pagination to display them in frontend. Sometimes I need to get them in a specific order, but I cant find a way to do that in QueryBuilder..

For example one query returns products by id: 70,71,72,73,74,75 But if I have some custom order I want to display it: 72,74,75,70,71,73

So in my querybuilder I do something like this:

public function querySortedProductsInCategories($id, $type, $slug, $sort)
{
    $qb = $this->createQueryBuilder('p');
    $qb->addSelect(array('p', 'gallery'));
    $qb->addSelect(array('p', 'media'));
    $qb->addSelect(array('p', 'image'));
    $qb->leftJoin("p." . $type, "c");
    $qb->leftJoin('p.gallery', 'gallery');
    $qb->leftJoin('gallery.galleryHasMedias', 'media');
    $qb->leftJoin('media.media', 'image');
    $qb->where("c." . $type. "= :id ");
    $qb->andWhere($qb->expr()->in('p.id', $sort));
    $qb->SetParameter('id', $id);
    return $qb->getQuery();
}

The generated query looks something like this:

'SELECT p, p, gallery, p, media, p, image FROM Mp\ShopBundle\Entity\Product p LEFT JOIN p.subcategory c LEFT JOIN p.gallery gallery LEFT JOIN gallery.galleryHasMedias media LEFT JOIN media.media image WHERE c.subcategory= :id  AND p.id IN('70', '73', '76', '72', '71', '74')'

But the returned array is stil ordered by Id...

If i try to do something like:

    $qb->orderBy("p.id", $sort);

But then ofcourse I get array to string conversion error...

What are the possible ways to do this?

Why do you select 4 times 'p' ? orderBy second argument can only be 'ASC' or 'DESC' — Alsatian, Jul 08 '16 at 07:06
Do not worry about the 4p`s... I know the orderBy can only be ASC or DESC, thats why im asking is it possible to order query results in my own way in the actual querybuilder — Dominykas55, Jul 08 '16 at 07:10
Take a look here : http://stackoverflow.com/questions/5957330/doctrine-2-mysql-field-function-in-order-by — Alsatian, Jul 08 '16 at 07:13

score 0 · Answer 1 · answered Jul 08 '16 at 11:21

public function querySortedProductsInCategories($id, $type, $slug, $sort)
{
    $qb = $this->createQueryBuilder('p');
    $qb->addSelect(array('p', 'gallery'));
    $qb->addSelect(array('p', 'media'));
    $qb->addSelect(array('p', 'image'));
    $qb->leftJoin("p." . $type, "c");
    $qb->leftJoin('p.gallery', 'gallery');
    $qb->leftJoin('gallery.galleryHasMedias', 'media');
    $qb->leftJoin('media.media', 'image');
    $qb->where("c." . $type. "= :id ");
    $qb->andWhere('p.id IN(:ids)');
    $qb->setParameter('ids', $id);
    $qb->orderBy('p.id', 'ASC'); // change to $sort if $sort either ASC or DESC
    return $qb->getQuery();
}

does this work?

score 0 · Accepted Answer · answered Jul 08 '16 at 12:06

You could do the trick with some overhead by using the indexBy parameter of the doctrine's queryBuilder from function

public function from($from, $alias, $indexBy = null);

this set the index of the resulting array to be the values of the field in this paramareter.

Then you can reorder this array in your way

public function querySortedProductsInCategories($id, $type, $slug, $sort)
{
    $qb = $this->createQueryBuilder('p');
    $qb->from('Product', 'p', 'p.id';
    [... the rest of your query]
    $result = $qb->getQuery()->getResult();
    // Here you have an array indexed by the id of your products and you can order it using your $sort array of products ids
    uksort($result,
        function ($key1,$key2) use ($sort) {
            $product1Position = array_search($key1,$sort);
            $product2Position = array_search($key2,$sort);
            if ( $product1Position === false || $product2Position === false) {
                return 0;
            }
            return ($product1Position < $product2Position) ? -1 : 1;
        }
    );
    return $result;
}

Sources: http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/reference/query-builder.html#high-level-api-methods http://php.net/manual/en/function.uksort.php

Good luck

Symfony2 query builder adding custom order to query

2 Answers2