8

In my Symfony/Doctrine app, I have a query that orders by RANDOM(). I call this same method several times, but it looks like the query's result is being cached.

Here's my relevant code:

$query = $table->createQuery('p')
    ->select('p.*, RANDOM() as rnd')
    ->orderBy('rnd')
    ->limit(1)
    ->useQueryCache(null)
    ->useResultCache(null);
$result = $query->fetchOne();

Unfortunately, the same record is returned every time, regardless of me passing null to both useQueryCache and useResultCache. I tried using false instead of null, but that didn't work either. Lastly, I also tried calling both setResultCacheLifeSpan(0) and setResultCacheLifeSpan(-1), but neither call made a difference.

Any insight on how to prevent caching since I want a different random row to be selected each time I call this method?

Edit: I also tried calling clearResultCache(), but that just ended up causing an error stating: "Result Cache driver not initialized".

Edit 2: As requested, here's the SQL generated by calling $query->getSqlQuery():

SELECT c.id AS c__id, c.name AS c__name, c.image_url AS c__image_url,
c.level AS c__level, c.created_at AS c__created_at, c.updated_at
AS c__updated_at, RANDOM() AS c__0 FROM cards c ORDER BY c__0 LIMIT 1
Matt Huggins
  • 81,398
  • 36
  • 149
  • 218
  • Hi Matt, have you tried `$query->useResultCache(false)`? – Darmen Amanbay Feb 07 '11 at 05:24
  • Yeah, I mentioned in my question that I tried using false, but that didn't work either. – Matt Huggins Feb 07 '11 at 14:27
  • 1
    Why disable the query cache? The query remains the same! You only need to clear the result cache. Maybe you are having your DBMS cache... you could log queries to see if a query is made for each request. – greg0ire Feb 07 '11 at 19:09
  • @greg0ire - You're right, my intent here was just to demonstrate that nothing I tried was working. I'll check into the DBMS cache though, perhaps that's why I'm seeing the same record returned each time. – Matt Huggins Feb 07 '11 at 20:36
  • Odd. What backend DBMS are you using? What's the actual SQL statement that's being generated from the Doctrine query? ($query->getSqlQuery())? What happens if you run that directly on your DBMS? I'm doing virtually the same thing using MySQL and it seems to work fine for me. – Matt Gibson Feb 08 '11 at 08:09
  • I agree, check the generated query because `RANDOM()` may be returning the same result every time. For example, if the same seed is given to MySQL's `RAND()` function it will return the same value every time. – rojoca Feb 08 '11 at 16:31
  • @Matt Gibson - I edited the question to include the SQL that's being output. You can see that RANDOM() is properly being called. It looks like RANDOM() is properly being used the first time it's called, as a random row is selected; however, each successive call then returns that same random row. – Matt Huggins Feb 08 '11 at 16:55

2 Answers2

5

It turns out I'm a moron. I tried to simplify my query for this question, and in doing so, I didn't capture the true cause. I had a where() and andWhere() call, and the combination of conditions resulted in only one possible record being matched. Thanks for taking the time to respond, everyone, sorry to have wasted your time!

Matt Huggins
  • 81,398
  • 36
  • 149
  • 218
4

Doctrine also caches entities you created in the same request/script run.

For instance:

$order = new Order();
$order->save();

sleep(10); // Edit this record in de DB in another procces.

$q = new Doctrine_Query();
$result = $q->select()
            ->from('Order o')
            ->where('o.id = '.$order->id);
$order = $result->getFirst();
print_r($order->toArray());

The print_r will not contain the changes you made during the sleep.

The following code will remove that kind of memory cache:

$manager = Doctrine_Manager::getInstance();
$connection = $manager->getCurrentConnection();
$tables = $connection->getTables();
foreach ( $tables as $table ) {
    $table->clear();
}

PS: Added this answer because I found this topic trying to resolve above issue.

ReenL
  • 96
  • 4