Elequent lazy and chunk method make data duplicate

[normatov07]

Laravel Version

10.10

PHP Version

8.2

Database Driver & Version

PostgreSql 16

Description

I am trying to export my data from postgresql to excel and I have huge data to export so I need to used laravel lazy collection. but I noticed some strange results. My some real rows are lost and instead some other rows is duplicated or even threefolded.

Steps To Reproduce

I am trying to export my data from postgresql to excel and I have huge data to export so I need to used laravel lazy collection. but I noticed some strange results. My some real rows are lost and instead some other rows is duplicated or even threefolded. Then I use chunk method but result was the same. then I get all data using get() method and I saw my real data successfully, nothing is changed.

Here is my code which I used:

$this->query->chunk(200, function (Collection $items) {
            foreach ($items as $item) {
                $item = $item->toArray();
                Log::info("row" , $item);
               
               .........
           }
        });

  foreach ($this->query->lazy(200) as $item) {
                $item = $item->toArray();
                Log::info("row",$item);
               .......
}

[Jacobs63]

How are your results ordered?

What exactly is $this->query?

[normatov07]

How are your results ordered?

What exactly is $this->query?

$this->query is model instance. one duplicate row order: firstly shown on 712-row, secondly 850-row, thirdly 1075-row. And this order is different for other duplicate rows.

Also despite of some rows are duplicate, the real number of rows was not changed, instead some real rows was lost.

[Jacobs63]

How are your results ordered?
What exactly is $this->query?

$this->query is model instance. one duplicate row order: firstly shown on 712-row, secondly 850-row, thirdly 1075-row. And this order is different for other duplicate rows.

Also despite of some rows are duplicate, the real number of rows was not changed, instead some real rows was lost.

My apologies, meant to ask how is your query ordered.

Could you perhaps show us the query - possibly via $query->dd() before using lazy or chunk?

[normatov07]

How are your results ordered?
What exactly is $this->query?

$this->query is model instance. one duplicate row order: firstly shown on 712-row, secondly 850-row, thirdly 1075-row. And this order is different for other duplicate rows.
Also despite of some rows are duplicate, the real number of rows was not changed, instead some real rows was lost.

My apologies, meant to ask how is your query ordered.

Could you perhaps show us the query - possibly via $query->dd() before using lazy or chunk?

here is query:

SELECT "table1".*,
       "table2"."contract_id" AS "contractId",
       "table3"."pin"          AS "pin",
       "table3"."first_name"     AS "firstName",
       "table3"."last_name"      AS "lastName",
       "table5"."id"           AS    "organizationId",
       "table5"."name"       AS    "organizationName",
       "table4"."id"           AS "table4Id",
       "table4"."name"         AS "table4Name"
FROM   "table1"
           INNER JOIN "table2"
                      ON "table1"."col" = "table2"."col"
           INNER JOIN "table3"
                      ON "table1"."col" = "table3"."id"
           INNER JOIN "table4"
                      ON "table3"."col" = "table4"."id"
           INNER JOIN "table5"
                      ON "table4"."col" = "table5"."id"
WHERE  "table1"."status" = 1
  AND "table5"."id" = '9c872fd6-631e-4a13-8270-9e57ca435727'
  AND "table1"."deleted_at" IS NULL
order by "table1"."created_at"

I just changed table names and field names.

[Jacobs63]

Interesting, so it is not just a model query, this more than that.

Is created_at ambiguous or not?
Inner join might create duplicates as well.

Could, perhaps, ordering by ID help? Did you try lazyByIdDesc or chunkByIdDesc?

[normatov07]

Interesting, so it is not just a model query, this more than that.

Is created_at ambiguous or not? Inner join might create duplicates as well.

Could, perhaps, ordering by ID help? Did you try lazyByIdDesc or chunkByIdDesc?

No it is not depend on Inner join, firstly I checked this. when I use $this->query->get() There was no duplicate rows. Also created_at is not ambiguous. it depend on table1 as you saw. I can not use order by id becouse id is not auto_increment it is uuid and that will not give me the same result.

[normatov07]

I used $this->query->cursor() it worked perfectly, but it is too slow in my case. are there any way fix this issue?

[rodrigopedra]

Have you tried using Builder@lazyById()?

Reference: https://laravel.com/docs/10.x/queries#streaming-results-lazily

Also, Laravel 10.x is unsupported, do you know if the issue is still present on Laravel 11.x?

[rodrigopedra]

Mind that Builder@lazyById() allows you to choose a custom column as its "id"

[normatov07]

Have you tried using Builder@lazyById()?

Reference: https://laravel.com/docs/10.x/queries#streaming-results-lazily

Also, Laravel 10.x is unsupported, do you know if the issue is still present on Laravel 11.x?

I think this is not fit using with joined tables. I use this $this->query->lazyByIdDesc(200, 'auto_payments.id') and got this:

Illuminate\Database\Eloquent\MissingAttributeException {#1605 // app/Exports/Spout/SpoutAutoPaymentExport.php:91
  #message: "The attribute [auto_payments.id] either does not exist or was not retrieved for model [App\Models\AutoPaymentModel]."
  #code: 0
  #file: "
/var/www/Projects/platform/vendor
/laravel/framework/
src/Illuminate/Database/Eloquent/Concerns/HasAttributes.php"
  #line: 475
}

And I can not use just id becouse there is another Ambiguous error

[rodrigopedra]

Can you try qualifying the column using an \Illuminate\Database\Query\Expression?

$this->query->addSelect([
    new \Illuminate\Database\Query\Expression('auto_payments.id AS sort_id')
])->lazyByIdDesc(200, 'sort_id')

You can also use the DB::raw() method from the DB façade.

[macropay-solutions]

Hi, we had the same issue in downloading big volume data and we solved it with cursor just like @normatov07 said here #52913 (comment) .

The issue was the data was changing while the download in chunks was happening so it resulted in duplicates while using pagination(simple or length aware).

The cursor paginator (after we fixed it) worked.
Note:
It has also a drawback. If you start downloading and you have the cursor referencing the column x.
If the cursor already fetched first page and you insert a new row that will fit in that page, that row will not be downloaded.

[macropay-solutions]

Another note: php excel spreadsheet can't stream data from memory to disk so it will fail for big volumes once your memory is filled. Use streamed responses and csv files to export the data.

[macropay-solutions]

( JsExcel can stream data from memory to disk).