Laravel 4.2 query error - Cannot execute queries while other unbuffered queries are active

Question

So using Laravel 4.2, I'm attempting to run a query on a very large database of 600,000 records. When doing so, I get the following error:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. (SQL: select 'username', 'timestamp', 'ip', 'appID' from 'UserLog' where 'id' > 1179525)

Here is my code:

public static function getUserLogData()
{
    $getUsers = DB::connection('PORTAL2')->table('UserLog')
                                         ->select('username', 'timestamp', 'ip', 'appID')
                                         ->where('id', '>', '1179525')
                                         ->get();

    $numOfRowsReturned = count($getUsers);

    if($numOfRowsReturned>0)
    {
        return $getUsers;
    }
    return 0;
}

To explain the magic number 1179525 , I'm using the ID to attempt to select only 10 rows, as there are only 10 id's after 1179525.

The query works just fine when the code looks like this (although I'd like to select ~500 rows, not 1):

public static function getUserLogData()
{
    $getUsers = DB::connection('PORTAL2')->table('UserLog')
                                         ->select('username', 'timestamp', 'ip', 'appID')
                                         ->where('id', '>', '1179525')
                                         ->first();

    $numOfRowsReturned = count($getUsers);

    if($numOfRowsReturned>0)
    {
        return $getUsers;
    }
    return 0;
}

Any ideas as to how to solve this issue?

Answer 1

Instead of ->get() use ->take(10) or any number to specify the limit on the number of rows returned. ->first() is really ->take(1).

Also instead of doing count($getUsers) and then checking if it is > 0, use the

`if (!empty($getUsers))` 

it will be more efficient with large data sets and does what you mean to do. count() will traverse the array, empty() will only check if it is not null or 0 length.