Pro Tip: Use an explicit LIMIT in Doctrine

Doctrine is usually pretty rad. And sometimes it’s really really dumb.

For example, in an imaginary CMS the following Doctrine query will grab a random blog post:

$random_post = Doctrine_Query::create()
    ->select('*')
    ->from('BlogPosts')
    ->where('published')
    ->orderBy('RANDOM()')
    ->fetchOne();

And it will work great when you first start writing blog posts. But as the total number of posts increases, the performance will get worse and worse. That’s because, apparently, when you ask Doctrine to “fetch one” that doesn’t quite convert to LIMIT 1 on the back end. It will actually select — and possibly instantiate an object for — everything that matches your query. After it’s done with all this work, Doctrine will hand you the first result.

If you want to save yourself headaches later, use something like this:

$random_post = Doctrine_Query::create()
    ->select('*')
    ->from('BlogPosts')
    ->where('published')
    ->orderBy('RANDOM()')
    ->limit(1)
    ->fetchOne();

Any time you call fetchOne(), be sure to explicitly use limit(1).