How to chunk results from a custom query in Larave

Question

I have a custom query that grabs data from the old system and maps it to models in the new system. The query looks like this:

$companies = DB::connection('legacy')->select("...");

And since it's a lot of data, I'd like to use Eloquent's chunk feature (just sample code copied from their docs):

User::chunk(200, function($users)
{
    foreach ($users as $user)
    {
        //
    }
});

How do I implement this?

---

Edit: My code now looks like this, which results in no response:

DB::connection('legacy')->select("SELECT * FROM companies")->chunk(200, function($companies) {
    foreach ($companies as $company) {
        // dd($company);
        $entity       = Entity::firstOrNew(['external_id' => $company->companyKey]);
        $entity->name = $company->companyName;
        $entity->save();
    }
});

Answer 1

The chunk feature is only available for Eloquent models and QueryBuilder requests, e.g.

DB::table('tbl')->where('num', '>', 3)->chunk(500, function($rows) {
    // process $rows
});

But it won't work for DB::select('...') request. You need to either use a QueryBuilder request, or use an underlying PDO object to query the database, e.g:

$pdo = DB::getPdo();
$sth = $pdo->prepare("SELECT ....");
$sth->execute();
while ($row = $sth->fetch(PDO::FETCH_ASSOC))
{
    // ...
}

Answer 2

None of these answers worked for me. I created my own function based on @deyes answer.

private static function chunk($query, $max, $function) {
    $counter = preg_replace('/SELECT (.*?) FROM/', 'SELECT COUNT(*) FROM', $query);
    $total = DB::connection('legacy')->select($counter)[0];
    $total = (array)$total;
    $total = $total['COUNT(*)'];

    $pages = ceil($total / $max);

    for ($i = 1; $i < ($pages + 1); $i++) {
        $offset = (($i - 1)  * $max);
        $start = ($offset == 0 ? 0 : ($offset + 1));
        $items = DB::connection('legacy')->select($query . ' LIMIT ' . $offset . ', ' . $max);

        $function($items);

        unset($items);
    }
}

Usage

YourClass::chunk('SELECT * FROM tablename', 50, function($items) {
    //Work with $items.
});

Please note that this a simple quick fix and your query probably has to be fairly simple as I'm using search-replace to build a count query and I'm just tacking on LIMIT X, Y to the end of the query but it works for me.

Answer 3

I believe you can use chunk on a query builder. E.g.

DB::connection('legacy')->select("...")->chunk(200, function($companies){
    //do something with $companies
});

Answer 4

Try using the orderBy clause:

DB::table('tbl')->where('num', '>', 3)->orderBy('id')->chunk(500, function($rows) {
    // process $rows
});

Answer 5

Update: March 2018 a new function was added to the query builder class. It's possible to achieve the same now using fromSub:

$subQuery = DB::table('users')->where(...);

DB::query()->fromSub($subQuery, 'alias')->orderBy('alias.id')->chunk(200, function ($chunk) {
    // Do something
});

And to use a different connection start with DB::connection('legacy')->query()

---

Old answer: Found this question by accident, but a little trick that might come handy in some cases.

$query = 'SELECT * FROM ... JOIN ... UNION ... WHATEVER ... GROUP BY';

// This is the important part:
$query = '(' . $query . ') somealias';

DB::connection('legacy')->table(DB::raw($query))->chunk(1000, function($rows){
    // Do something
});

The query laravel executes then goes like this:

select * from (...) somealias LIMIT ... OFFSET ...

This should work at least in Laravel 5.1. But I don't see a reason why it shouldn't work in 4+.

Answer 6

deyes's answer has a bug.

AS-IS

if 'legacy' table has 'id' column and there's 1,2,3,4,5 ... 100 numbered data.

<?php

$max = 10;
$total = DB::connection('legacy')->select("...")->count();
$pages = ceil($total / $max);
for ($i = 1; $i < ($pages + 1); $i++) {
    $offset = (($i - 1)  * $max);
    $start = ($offset == 0 ? 0 : ($offset + 1));
    $legacy = DB::connection('legacy')->select("...")->skip($start)->take($max)->get();
    /* Do stuff. */

    $legacyIds = $legacy->lists("id");
    echo "i = " . $i . ": \n";
    print_r($legacyIds);
}

//Result
i = 1: 
Array
(
    [0] => 1
    [1] => 2
    [2] => 3
    [3] => 4
    [4] => 5
    [5] => 6
    [6] => 7
    [7] => 8
    [8] => 9
    [9] => 10
)
i = 2: 
Array
(
    [0] => 12
    [1] => 13
    [2] => 14
    [3] => 15
    [4] => 16
    [5] => 17
    [6] => 18
    [7] => 19
    [8] => 20
    [9] => 21
) ...

TO-DO

$max = 10;
$total = DB::connection('legacy')->select("...")->count();
$pages = ceil($total / $max);
for ($i = 1; $i < ($pages + 1); $i++) {
    $offset = (($i - 1)  * $max);
    $legacy = DB::connection('legacy')->select("...")->skip($offset)->take($max)->get();
    /* Do stuff. */
}