Symfony2 Doctrine 从一个类别中获取随机产品

2022-01-16 00:00:00 php symfony doctrine entity relationships

我有以下数据库方案:

table 'products'
id
category_id

当然还有一个类别表，只有一个 id.

and of course a category table, just with an id.

数据看起来像这样:

Products
--------------------
| id | category_id |
--------------------
| 0  | 1           |
| 1  | 1           |
| 2  | 1           |
| 3  | 2           |
| 4  | 2           |
| 5  | 1           |
--------------------

我想选择一个类别(例如类别 1)，因此我在我的产品存储库类中选择该类别中的所有行:

I would like to select a category(for example Category 1), so I select all the rows from that category in my product-repository class:

return $this
    ->createQueryBuilder('u')
    ->andWhere('u.category = :category')
    ->setMaxResults(1)
    ->setParameter('category', $category->getId())
    ->getQuery()
    ->getSingleResult()
;

我现在如何选择随机产品?另外:是否可以通过关系解决这个问题?

How I can select now a random product? Also: Is it possible to solve this via Relationships?

我在实体Category"和Product"之间有一个 OneToMany 关系，所以我也可以通过 category->getProducts()...

I have a OneToMany Relationship between the entities "Category" and "Product", so I could also get all the products via category->getProducts()...

任何帮助都会非常有用，谢谢

Any help would be really useful, thanks

推荐答案

你首先要统计产品的总数，然后生成一个随机偏移量来选择一个随机产品.

You first have to count the total number of products, then generate a random offset to select a random product.

这应该让你开始:

$count = $this->createQueryBuilder('u')
             ->select('COUNT(u)')
             ->getQuery()
             ->getSingleScalarResult();

然后你可以在你的 1 和总行数之间生成一个随机数.

And then you can generate a random number between your 1 and the total number of rows.

return $this->createQueryBuilder('u')
    ->where('u.category = :category')
    ->setFirstResult(rand(0, $count - 1))
    ->setMaxResults(1)
    ->setParameter('category', $category->getId())
    ->getQuery()
    ->getSingleResult()
;

翻译为:

SELECT * FROM products WHERE category_id = ? LIMIT 1, {random offset}

相关文章