[BUG] FromQuery - Sort Bug Showing Duplicated Data

[RetailAid]

Versions Laravel version: 6.2 Php version: 7.4.16 Laravel-Excel version: 3.1.27

Description: When using FromQuery to do a custom query and using the sort function on the Eloquent object (users table), some of the rows are duplicated.

Expected behaviour: All the relevant rows in the database should show correct data. Users in the table are unique.

Code screenshot First line produces incorrect result 2nd line produces correct data with no duplicates.

The code is part of an UsersExport class which implements FromQuery, WithHeadings, WithMapping

[patrickbrouwers]

I think this is because we use chunk() instead of chunkById I'm not sure if we can just switch that.

[eldor]

Hi,

Laravel: 8.56.0 PHP: 8.0 Laravel-Excel: 3.1.30

We see the same behaviour with randomly duplicate rows with large data.

[rderks88]

Same issue here. Confirmed that chunking is the issue. Issue is resolved when set chunk size > # rows.

[N3OGeorgy]

Hello,

Versions Laravel version: 9.19 Php version: 8.1 Laravel-Excel version: 3.1.40

Same issue here. I generated 5 exports with "orderBy" in query, records in all of them were scrambled and 1 export contained duplicates. I generated 5 exports removing the "orderBy" and all of them contained data in same order and no duplicate records found.

[gioacchinopoletto]

Same issue also for me, with Laravel-Excel version 3.1.44. Configuration is the same of @N3OGeorgy

[Tofandel]

This is actually normal, if you run this same query with pagination, it will return random rows, because the order clause is not producing a unique value for each row, so mysql will return all the rows with the exact same value in whatever order it wants, you simply need to add a second order by clause on an unique column

You need to ->orderBy('membership_start', 'desc')->orderBy('id')

Possibly we could add an orderBy key to the given query if we don't find it already, but if it's already ordering by a unique key that is not primary it might come at a performance cost on the query

In laravel's chunk method you'll find

    /**
     * Add a generic "order by" clause if the query doesn't already have one.
     *
     * @return void
     */
    protected function enforceOrderBy()
    {
        if (empty($this->query->orders) && empty($this->query->unionOrders)) {
            $this->orderBy($this->model->getQualifiedKeyName(), 'asc');
        }
    }

So I'd just classify it as undesired behavior and a bug of mysql and not this lib, leave it as is and add a note in the doc about the fact the orderBy of FromQuery must be unique if you don't want skipped rows and duplicated rows