CakePHP / mysql：select found_rows无法在循环中运行？

Question

I'm using CakePHP and trying to retrieve FOUND_ROWS() for a query that runs several times in a loop (each time with a different WHERE section). The strangest thing is happening: it returns the correct result for the first query - and then the same exact result for subsequent queries.

This is the code:

$article = new Article();
    $query = $db->buildStatement(
        array(
        'table' => $db->fullTableName($article)
        , 'alias' => 'Article'
        , 'order' => '`Article`.`publish_date` desc'
        , 'offset' => $startIndex
        , 'joins' => array(
                        array(
                            'table' => 'articles_categories',
                            'alias' => 'ArticlesCategories',
                            'type' => 'inner',
                            'conditions' => array(
                                'ArticlesCategories.article_id = Article.id',
                            ),
                        )
                        )
        , 'limit' => $this->maxNumArticlesToLoadPerPage
        , 'conditions' => "ArticlesCategories.category_id = $categoryId and publish_date <= now()"
        , 'fields' => array('Article.id', 'Article.name', 'Article.search_engine_teaser', 'Article.image_file_name', 'Article.content_modified', 'ArticlesCategories.category_id')),
    $article
    );

    $query = str_replace('SELECT', 'SELECT SQL_CALC_FOUND_ROWS', $query);
    $articles = $article->query($query);

    // also return how many total articles are in this category
    $numArticlesInCategory = $article->query('select found_rows()')[0][0]['found_rows()'];

Now, where real curiosity here is that I used ET GLOBAL general_log = 'ON' in the mysql database to track down what's going on, and it appears that the first query is indeed run several times in a loop, but the query select found_rows() is only run once!

I also tried to run select SQL_NO_CACHE found_rows(), but that didn't help.

Has anyone encountered this?

Answer 1

Check your models $cacheQueries property setting, it might be true, causing the query to be cached on the CakePHP side. Use the second query() parameter to disable caching per query:

query('sql', false)

Btw., ArticlesCategories.category_id = $categoryId looks like a possible SQL injection vulnerability, you should never insert data in queries that way, always use the key => value syntax for conditions, or use prepared statements!

Also model classes shouldn't be instantiated directly, instead ClassRegistry::init() should be used.

See also

• Cookbook > Models > Retrieving Your Data > Model::query()
• Cookbook > Models > Model Attributes > cacheQueries