如何从doctrine查询构建器得到部分结果 [英] How to get partial result from doctrine query builder

问题描述

我有一个产品实体，其中有一个数组作为属性：

I have a product entity in which it has an array as attributes:

     /**
     * @ORM\OneToMany(targetEntity="Shopious\MainBundle\Entity\ProductPicture", mappedBy="product", cascade={"persist","remove"})
     */
    protected $pictures;

    /** 
    * @Accessor(getter="getCover") 
    */
    private $cover;
    public function getCover()
    {
        if($this->pictures->count() > 0) {
            return $this->pictures[0];
        }
        return new ProductPicture();
    }

现在在我的查询生成器中，我有以下代码：

Now in my query builder, I have the following code:

 $query = $em->createQueryBuilder()->select('p')
            ->from("SiteMainBundle:Product", 'p')
            ->innerJoin('p.category', 'c')
            ->innerJoin('p.shop', 'shop')
            ;

这里的问题是我不想选择p的所有属性。所以我只想在图片数组中获得第一个ProductPicture（在上面的例子中，它类似于getCover（）方法）。我如何做到这一点？

The issue here is that I don't want to be selecting all of p's attribute. So I only wanted to get the first ProductPicture in the pictures array (in my case above it's similar to the getCover() method). How do I do this?

到目前为止，我可以过滤出我想做的部分属性：

So far I can filter out the the partial attributes that I want by doing:

 $query = $em->createQueryBuilder()->select('p.name, p.id')
                ->from("SiteMainBundle:Product", 'p')
                ->innerJoin('p.category', 'c')
                ->innerJoin('p.shop', 'shop')
                ->innerJoin('p.pictures', 'pictures')
                ;

所以在上面的例子中，我已经做了内在的图片，但是如何获得第一个元素从这里？

so in the example above I have done inner joined on the pictures, but how do I get the first element from here?

总而言之，我的问题是如何使用查询构建器选择/查询图片数组中的第一个ProductPicture？因为当我这样做：

In conclusion, my question is how do I select/query the first ProductPicture in the pictures array using the query builder? Because when I do:

$ query = $ em-> createQueryBuilder（） - > select（'p'）

$query = $em->createQueryBuilder()->select('p')

它返回整个产品属性，但我不想要整个产品的属性..我只想要其中的一些，如id，name等。然而其中一个产品属性实际上是一个实体（其中是ProductPicture），那么如何在select语句中返回？

it returns the whole product attributes, but I don't want the whole product attributes.. I only wanted some of them, such as the id, name, etc. However one of the product attributes is actually an entity (which is the ProductPicture), so how do I return this in the select statement?

编辑：

这是一个SQL等效的图片应该如何内部加入：

Here's a SQL equivalent on how the pictures should be inner joined:

SELECT * 
FROM  `product` 
JOIN  `product_picture` ON  `product`.id =  `product_picture`.product_id
WHERE  `product`.id =100
LIMIT 1

推荐答案

尝试这样的东西，如果它是一对多，正常的mySQL行为返回几个记录冗余的产品数据，如果同样的情况发生在这里，那么只返回第一个记录应该做的窍门。

Try something like this, if it's a one to many, the normal mySQL behaviour is returning several records with redundant product data, if the same case happens here, then only returning the first record should do the trick.

PS: assuming the ProductPicture entity has a url property that you want to get

$query = $em->createQueryBuilder()->select('p.id, p.name, pictures.url')
            ->from("SiteMainBundle:Product", 'p')
            ->innerJoin('p.category', 'c')
            ->innerJoin('p.shop', 'shop')
            ->innerJoin('p.pictures', 'pictures')
            ;

这篇关于如何从doctrine查询构建器得到部分结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！