CODEWITHSUNDEEP
phpsqllaravel-4eloquent
Benubird
Benubird

Reputation: 19507

How to get one line at a time from eloquent query in laravel?

Say I have a simple query to get all rows from a table:

App::make('My_Model')->get();

But, I have a huge number of rows - say 100,000, so many that PHP runs out of memory trying to read them all in.

For some reason I want to go through each of these rows, and do some operation based on them.

Is there any way to tell laravel to give me one row at a time, the way the old mysql_fetch_row worked?

Note: I'm currently solving this by using limit() commands to get 5000 rows at once, which works fine, except that it repeats the query 20 times.

I want to know if there is any built in method in laravel or eloquent to give me the next row, instead of all rows ?

Upvotes: 8

Views: 6748

Answers (4)

Matt Brown
Matt Brown

Reputation: 81

For anybody landing here using Laravel 5.2+ (like I did) There is now a 'cursor' method available which returns a Generator

https://laravel.com/api/5.2/Illuminate/Database/Query/Builder.html#method_cursor

So instead of ->get() use ->cursor()

Upvotes: 8

dspitzle
dspitzle

Reputation: 770

What kind of operation are you trying to perform? The way you describe it you are loading all 100,000 records into active memory, but there is almost no reason you should need to do that unless you actually want to display all 100,000 records.

  • If you want to change some but not all of the records it should be possible to build an DB::update() query to do it, so you don't need to load the records into PHP's memory at all
  • If you want to only display certain records it should be possible to modify your $model query so you only load the records into PHP's memory that you actually need.
  • If you need to actually display the whole recordset, then you will in fact need to iterate through them piecemeal, but that doesn't sound like what you're trying to do.

Give us more information, what are you actually trying to accomplish?

Upvotes: 0

Antonio Carlos Ribeiro
Antonio Carlos Ribeiro

Reputation: 87769

The problem is, what is the next row in an application that lives shortly? Unless you are building a command line application that has a cursor, you application lives just for a request. Laravel can give you ways to get a subset of your cursor, so if you need 5000 lines, you can get those 5000 lines at the same time.

So you can use paginate:

$someUsers = User::where('votes', '>', 100)->paginate(5000);

Take and skip:

$page = 5;

$pageSize = 5000;

$users = User::skip($page * $pageSize)->take($pageSize)->get();

Chunk, internally uses paginagion, but it's a little bit restricting, because it will force you to process your whole cursor:

User::chunk(5000, function($users)
{
    /// every time this clojure is ran you get a new subset of 5000 rows.

    foreach ($users as $user)
    {
        // do whatever you need with them
    }
});

Sidenote: you can save memory by turning query log off:

DB::disableQueryLog();

Upvotes: 1

msturdy
msturdy

Reputation: 10794

If you want to process them, a thousand at a time say, the chunk() method in Eloquent should work for you:

My_Model::chunk(1000, function($my_models)
{
    foreach ($my_models as $my_model)
    {
        // do something
    }
});

More in the Eloquent docs

Depends on what you are doing to your data...

Upvotes: 5

Related Questions