Laravel cursor and lazy method working concepts

I am wondering both methods concepts which running query. I searched but I can not found enough information about them. does cursor use special database cursor method for example mysql cursor...? or run every time select for one row? and similar question for lazy method ?

martinbean

1 year ago

Level 80

@normatov13 The lazy method will query “chunks” of rows at a time, and then return that “chunk” as a collection of Eloquent records.

Article::query()->chunkById(function ($articles) {
    foreach ($articles as $article) {
        // Do something with article in chunk...
    }
});

The cursor method does a single database query, but will only convert one row at a time to an Eloquent model:

$articles = Article::query()->cursor();

foreach ($articles as $article) {
    // Do something with article...
}

When iterating over large data sets I prefer to use the chunk approach. It just makes more sense to load say, 1000 rows at a time, iterate over those, and then load the next 1000. With the cursor approach, if you have a large data set, then it’s still going to pull back all rows, and they still need holding in memory somewhere. If you have say, millions of rows, this could cause problems.

2 likes

normatov13

1 year ago

Level 2

@martinbean Yeah I read about these. But question is "does this methods use database cursor for example mysql cursor, postgresql cursor or something like them ?". how do they use sql query ? or what query generated at the end ?

jlrdw

1 year ago

Level 75

@normatov13

Cursor Pagination

https://laravel.com/docs/11.x/pagination#cursor-pagination

From Documentation:

This method of pagination is particularly well-suited for large data-sets and "infinite" scrolling user interfaces.

If that's what you are referring to. Lookup how it works in the API.

Edit:

For most everything regular limit and offset pagination works fine.

Query what you need, not all records at one time.

2 likes

Tray2

1 year ago

Best Answer

Level 73

There are basically two types of cursors, implicit and explicit. I always mix them up but one of them handles a single result, and the other a set of results. They make more sense in a procedure or function in the database. In the PL/SQL language you can do this.

--Implicit cursor
SELECT * 
INTO vTableOneRow
FROM table_one
WHERE id = 1;

The code above fetches one record and one record only.

The other method is to define a cursor

--Explicit cursor
DECLARE
CURSOR tableOneCursor IS
  SELECT *
  FROM table_one;

vTableOneRow table_one%ROWTYPE; 

BEGIN
 OPEN tableOneCursor;

 LOOP
    FETCH  tableOnecursor  INTO vTableOneRow;
    EXIT WHEN c_sales%NOTFOUND;

    -- Process each record

 END LOOP;
 CLOSE tableOneCursor;
END;

There has been some debate that only implicit cursors should be used, but I'd prefer to use the one that fits the need best.

However, this is on database level, and the cursors you are referring to is on php level, but basically they work the same way, one fetched all the data needed in one go, and the other fetches a defined number of records on each iteration.

3 likes

Please or to participate in this conversation.