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?