CSV Export Loses Global Sort Order Due to Using Chunks (Observed with PostgreSQL)

[kaspernowak]

Package

filament/filament

Package Version

v3.2.116

Laravel Version

v11.29.0

Livewire Version

v3.5.12

PHP Version

PHP 8.2.22

Problem description

When exporting data to CSV using Filament's Export action, the global sorting order is not maintained when the data is processed in chunks. This issue has been observed with PostgreSQL under these conditions:

1. The chunk size is smaller than the total number of records
2. Any sort order is applied, whether through:
   • Custom sort via modifyQueryUsing()
   • Default table sort configuration
   • User-triggered column sorting in the Filament table

Importantly, the sorting works correctly when the chunk size is larger than the total number of records, confirming this is specifically a chunking issue rather than a general sorting problem.

Current workaround:

• Setting the chunk size larger than the total number of records resolves the issue
• However, this is not a scalable solution for large datasets

Additional context:
The issue appears to be related to how ORDER BY clauses are handled during chunked queries. The fact that the sorting works correctly when processing all records in a single chunk suggests that the chunking mechanism isn't properly maintaining the global sort order when splitting the data.

This affects all sorting methods available in Filament:

• Default table sorting
• Interactive column sorting
• Custom query modifications

This might require either:

1. A modification to how chunks are processed to maintain global order
2. A database-specific implementation of the chunking strategy
3. A warning in the documentation about this limitation

Environment:

• Database: PostgreSQL 14.x
• Records: Works correctly when chunk size > total records
• Sorting: Affects all available sorting methods in Filament

Expected behavior

The CSV export should maintain a consistent global sort order regardless of:

1. The chunk size used
2. The total number of records
3. How the sort order was specified (default table sort, column sorting, or custom sort)
4. The specific sort order applied

The chunking process should preserve the global ordering while still allowing for efficient processing of large datasets.

Steps to reproduce

See ReadMe in Reproduction repository.

Reproduction repository (issue will be closed if this is not valid)

https://github.com/kaspernowak/filament-issue

Relevant log output

No response

[github-actions]

Hey @kaspernowak! We're sorry to hear that you've hit this issue. 💛

However, it looks like you forgot to fill in the reproduction repository URL. Can you edit your original post and then we'll look at your issue?

We need a public GitHub repository which contains a Laravel app with the minimal amount of Filament code to reproduce the problem. Please do not link to your actual project, what we need instead is a minimal reproduction in a fresh project without any unnecessary code. This means it doesn't matter if your real project is private / confidential, since we want a link to a separate, isolated reproduction. That would allow us to download it and review your bug much easier, so it can be fixed quicker. Please make sure to include a database seeder with everything we need to set the app up quickly.

[github-actions]

Thank you for providing reproduction steps! Reopening the issue now.

[bzy107]

I will write about what I have discovered through my investigation.

I analyzed the SQL queries executed by MySQL and PostgreSQL. Please note that the investigation is still ongoing.
I will update this document as progress is made.

First, regarding the differences in the SQL queries executed by MySQL and PostgreSQL:
It was observed that PostgreSQL performs an ORDER BY using the id. As a result, it seems to sort by chunks.

-- MySQL
SELECT DISTINCT * FROM `patients` WHERE `patients`.`is_valid` = true ORDER BY `name` ASC LIMIT 100 OFFSET 0;
-- PostgreSQL
select distinct on ("patients"."id") * from "patients" where "patients"."is_valid" = true order by "patients"."id" asc, "name" asc limit 100 offset 0

Since I don't have much time, I will proceed with the investigation gradually and report my findings.