Symfony2 Doctrine get random product from a category

Question

I have the following database scheme:

table 'products'
id
category_id

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

The data look something like that:

Products
--------------------
| id | category_id |
--------------------
| 0  | 1           |
| 1  | 1           |
| 2  | 1           |
| 3  | 2           |
| 4  | 2           |
| 5  | 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?

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

score 12 · Accepted Answer · edited Oct 22 '14 at 13:45

12

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

This should get you started:

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

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

Which translates to:

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

edited Oct 22 '14 at 13:45

totophe

2,961
1
15
12

answered May 18 '13 at 20:59

Pier-Luc Gendreau

13,553
4
58
69

Should `getResult` on the count query not be `getSingleScalarResult`? – Machiel Feb 17 '14 at 10:21
I know this snippet worked when I answered, but I haven't touched Doctrine in a while so things may have changed. – Pier-Luc Gendreau Feb 18 '14 at 00:20
1

Here's a ready to use function: https://gist.github.com/Thinkscape/124d658e4076421c0516 – Artur Bodera Jul 26 '14 at 13:39
1

I think the first query to determine count must determine the count of the products *in the given category*, not the total number of products: `$count = $this->createQueryBuilder('u')->select('COUNT(u)')->where('u.category = :category')->setParameter('category', $category->getId())->getQuery()->getSingleScalarResult();` Otherwise, the second query fails, if the random number exceeds the number of products in the category. – aimfeld Jul 17 '17 at 09:28

score -1 · Answer 2 · answered Mar 03 '17 at 21:58

Use this helper function:

<?php
use Doctrine\ORM\EntityManager;

/**
 * Retrieve one random item of given class from ORM repository.
 * 
 * @param EntityManager $em    The Entity Manager instance to use
 * @param string        $class The class name to retrieve items from
 * @return object
 */ 
function getRandomDoctrineItem(EntityManager $em, $class)
{
    static $counters = [];
    if (!isset($counters[$class])) {
        $this->counters[$class] = (int) $this->manager->createQuery(
            'SELECT COUNT(c) FROM '. $class .' c'
        )->getSingleScalarResult();
    }
    return $em
        ->createQuery('SELECT c FROM ' . $class .' c ORDER BY c.id ASC')
        ->setMaxResults(1)
        ->setFirstResult(mt_rand(0, $counters[$class] - 1))
        ->getSingleResult()
    ;
}

Example usage:

$randomItem = getRandomDoctrineItem($em, 'Application\Entity\Post');

Symfony2 Doctrine get random product from a category

2 Answers2

Linked