How to get database resource instead of array from Laravel Query Builder?

After @deczo pointed out an undocumented function ->chunk(), I dug around in the source code a bit. What I found is that ->chunk() is a convenience wrapper around multiplying my query into several queries queries but automatically populating the ->step($m)->take($n) parameters. If I wanted to build my own iterator, using ->chunk with my data set, I'd end up with 30,000 queries on my DB instead of 1.

This doesn't really help, too, because ->chunk() takes a callback which forces me to couple my looping logic at the time I'm building the query. Even if the function was defined somewhere else, the query is going to happen in the controller, which should have little interest in the intricacies of my View or Presenter.

Digging a little further, I found that all Query Builder queries inevitably pass through \Illuminate\Database\Connection#run.

// https://github.com/laravel/framework/blob/3d1b38557afe0d09326d0b5a9ff6b5705bc67d29/src/Illuminate/Database/Connection.php#L262-L284

/**
 * Run a select statement against the database.
 *
 * @param  string  $query
 * @param  array   $bindings
 * @return array
 */
public function select($query, $bindings = array())
{
  return $this->run($query, $bindings, function($me, $query, $bindings)
  {
    if ($me->pretending()) return array();

    // For select statements, we'll simply execute the query and return an array
    // of the database result set. Each element in the array will be a single
    // row from the database table, and will either be an array or objects.
    $statement = $me->getReadPdo()->prepare($query);

    $statement->execute($me->prepareBindings($bindings));

    return $statement->fetchAll($me->getFetchMode());
  });
}

See that nasty $statement->fetchAll near the bottom ?

That means arrays for everyone, always and forever; your wishes and dreams abstracted away into an unusable tool Laravel Query Builder.

I can't express the valley of my depression right now.

One thing I will say though is that the Laravel source code was at least organized and formatted nicely. Now let's get some good code in there!

Use chunk:

DB::table('highscores')
   ->select(...)
   ->orderBy(...)
   ->chunk($rowsNumber, function ($portion) {
      foreach ($portion as $row) { // do whatever you like }
   });

Obviously returned result will be just the same as calling get, so:

$portion; // array of stdObjects

// and for Eloquent models:
Model::chunk(100, function ($portion) {
    $portion; // Collection of Models
});

Here is a way to use the laravel query builder for making the query, but to then use the underlying pdo fetch to loop over the record set which I believe will solve your problem - running one query and looping the record set so you don't run out of memory on 30k records.

This approach will use all the config stuff you setup in laravel so you don't have to config pdo separately.

You could also abstract out a method to make this easy to use that takes in the query builder object, and returns the record set (executed pdo statement), which you would then while loop over as below.

$qb = DB::table("highscores")
        ->select("player_id", "score")
        ->orderBy("score", "desc");

$connection = $qb->getConnection();

$pdo = $connection->getPdo();

$query = $qb->toSql();
$bindings = $qb->getBindings();

$statement = $pdo->prepare($query);
$statement->execute($bindings);

while ($row = $statement->fetch($connection->getFetchMode()))
{
    // do stuff with $row
}