Preventing Doctrine's query cache in Symfony

Question

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

Answer 1

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!

Answer 2

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.