Doctrine 2 Randomly Selecting a Row (Offset 0 or 1 indexed)?

Question

So far, I think doctrine doesn't have a way of selecting a random row. So I am thinking I have a query to get the count of rows

// pseudo code
$count = SELECT COUNT(i) FROM Item i WHERE ...

Then have a real query to get the item using a random offset from PHP put into setFirstResult

$item = (SELECT i FROM Item WHERE ...)->setMaxResults(1)->setFirstResult(rand(0, $count))->getSingleResult()

Question is, my rand() do I start from 0 or 1? Then the end? $count or $count-1?

Gotama

Answer 1

setFirstResult() is 0-based.

Following your approach, you have to use:

->setFirstResult(rand(0, $count - 1))->setMaxResults(1)->getSingleResult();

---

Source:

I agree the documentation is unclear on that point. However, we can see that Doctrine\DBAL\Query\QueryBuilder uses it that way:

->modifyLimitQuery($query, $this->maxResults, $this->firstResult);

Which is then translated to SQL in Doctrine\DBAL\Platforms\AbstractPlatform:

final public function modifyLimitQuery($query, $limit, $offset = null)
{
    ...
    $query .= ' OFFSET ' . $offset;

OFFSET being 0-based in SQL, we can deduct that setFirstResult() is 0-based as well.

Berley

Answer 2

I use in my app:

$item = (SELECT i FROM Item WHERE ...)
    ->setMaxResults($count)
    ->setFirstResult(1)->getSingleResult();

start to count from 1 until the total of records

Nashoma

Does this select a random row? Or did you mean first result starts from 1? So to select a random row, I'd do ->setMaxResults(1)->setFirstResult(rand(1,$count))? – Gotama

I mean that the first result is 1 and the last is the total – Nashoma

Perhaps you misunderstood me, I want a single random row – Gotama

Answer 3

No matter you start from 0 or 1 , you must take the respective end to count and count-1 respectively

Ginkgo

Sorry, what do you mean? I think theres definately a difference if I use 0 or 1? – Gotama

yes, there is a difference, the interpretation goes on with its own way of specification..r8..??? and hence it depends upon the usage – Ginkgo