如何从学说查询生成器中获取部分结果

2022-01-16 00:00:00 sql php symfony doctrine query-builder

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

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

     /**
     * @ORMOneToMany(targetEntity="ShopiousMainBundleEntityProductPicture", 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、名称等.但是其中一个产品属性实际上是一个实体(其中是 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行为是返回多条带有冗余产品数据的记录，如果这里发生同样的情况，那么只返回第一条记录就可以了.

PS:假设 ProductPicture 实体有一个您想要获取的 url 属性

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')
            ;

相关文章