代码之家 › 专栏 › 技术社区 › Dominykas55

Symfony2 QueryBuilder按id顺序返回产品查询

query-builder symfony mysql php

Dominykas55 · 技术社区 · 8 年前

我有一个按任意顺序排列的ID列表:

array(57, 12, 29, 25, 11)

使用Symfony的QueryBuilder,我只需要从所有数据库对象中返回这些产品,它们应该按照特定的顺序返回。

我需要退还查询

我正在尝试各种不同的方法:

    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.status = 1 ");
        $qb->SetParameter('id', $id);
//        $qb->andWhere($qb->expr()->in('p.id', $sort));
//        $qb->andWhere("p.id IN (:sort) ");
//        $qb->SetParameter('sort', $sort);
        return $qb->getQuery();
    }

我试过了 IN 语句……它返回我需要的列表,但顺序不正确。

更新:

试图创建DQL函数,但出现错误。第一次这样做,我不知道发生了什么。。。

class Field extends \Doctrine\ORM\Query\AST\Functions\FunctionNode
{

    /**
     * @override
     */
    public function parse(\Doctrine\ORM\Query\Parser $parser) {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->stringPrimary = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->stringSecondary = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->stringThird = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }


    /**
     * @param \Doctrine\ORM\Query\SqlWalker $sqlWalker
     *
     * @return string
     */
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        // TODO: Implement getSql() method.
    }

}

问题:

        $em = $this->getEntityManager();
        $doctrineConfig = $em->getConfiguration();
        $doctrineConfig->addCustomStringFunction('FIELD', 'Mp\ShopBundle\Doctrine\Field');

        $qb = $this->createQueryBuilder('p');
        $qb->addSelect(array('p', 'gallery'));
        $qb->addSelect(array('p', 'media'));
        $qb->addSelect(array('p', 'image'));
        $qb->addSelect(array("p, field(p.id, " . implode(", ", $sort) . ") as HIDDEN field"));
        $qb->leftJoin("p." . $type, "c");
        $qb->leftJoin('p.gallery', 'gallery');
        $qb->leftJoin('gallery.galleryHasMedias', 'media');
        $qb->leftJoin('media.media', 'image');
//        $qb->where("p.id = :sort ");
        $qb->where("c." . $type. "= :id ");
        $qb->andWhere($qb->expr()->in('p.id', $sort));
        $qb->andWhere("p.status = 1 ");
        $qb->setParameter('id', $id);
//        $qb->setParameter('sort', $sort);
        $qb->orderBy('field');
        return $qb->getQuery();

错误:

Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '23'

问题:

[1/2] QueryException: SELECT p, p, gallery, p, media, p, image, p, field(p.id, 23, 40, 30, 24, 42, 37, 38, 58, 33, 8, 34, 35, 36, 28, 51, 14, 1) as HIDDEN field 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(23, 40, 30, 24, 42, 37, 38, 58, 33, 8, 34, 35, 36, 28, 51, 14, 1) AND p.status = 1 ORDER BY field ASC

2 回复 | 直到 8 年前

Community CDub 7 年前

使用MySQL,您可以创建一个能够使用的DQL函数 FIELD :

参见另一个类似问题:

Doctrine 2 mysql FIELD function in order by

ka_lin 8 年前

您可以根据具体情况订购:

$customOrderString = $this->generateCase($ids);
if(!empty($customOrderString)) {
    $db->addSelect("(CASE {$customOrderString} ELSE 3 END) AS HIDDEN ORD ");
    $db->orderBy('ORD', 'DESC');
}
return $qb->getQuery();

 .....
private generateCase($ids) {
    $caseString = '';
    foreach($ids as $index=>$id) {
        $caseString .= "WHEN $id THEN $index";
    }
    return $caseString;
}