How to chunk results from a custom query in Laravel
When migrating data from an old system, you often end up running raw SQL queries against legacy connections. A common pattern involves fetching massive result sets and mapping them to your Eloquent models. If you try to load the entire dataset into memory before processing it, you risk crashing your application with an OutOfMemory error. The solution is usually chunk(), but applying it to a raw DB::connection()->select() query requires a specific approach that differs from standard Eloquent collections.
The core issue in many attempts involves how the collection is instantiated. When you call $query->chunk(), Laravel expects the underlying object to implement specific traits related to pagination and chunking. A raw select statement returns a Collection instance, which does not support the chunk() method natively because it lacks the necessary query context to manage execution steps efficiently.
To make this work, you must wrap the raw result in a new Collection or, more effectively, use the DB::connection()->cursor() method. The cursor() approach is generally preferred for large datasets because it yields results one at a time rather than buffering them into memory. However, if you prefer to stick with the chunk() syntax you are familiar with from Eloquent models, you can still achieve the same result by forcing the collection to behave like a paginated resource.
Here is how you should structure the query to ensure it processes in chunks:
DB::connection('legacy')
->select("SELECT * FROM companies")
->chunk(200, function ($companies) {
foreach ($companies as $company) {
// Map legacy data to your new Eloquent model
$entity = Entity::firstOrNew(['external_id' => $company->companyKey]);
$entity->name = $company->companyName;
// Only update if changes were made to avoid unnecessary DB writes
if ($entity->isDirty()) {
$entity->save();
}
}
});
The critical detail here is understanding that chunk() works by executing the raw SQL and processing the results in batches. The closure passed to chunk() receives a Collection of models or arrays for each batch. In your specific case, since you are using select(), the items inside the collection will be plain objects containing only the selected columns. You must access these properties directly (e.g., $company->companyName) rather than trying to cast them to Eloquent models automatically, as that would require additional logic and potentially slower performance.
One significant pitfall in your original attempt was the lack of an if ($entity->isDirty()) check before saving. In a loop processing hundreds or thousands of records, creating a new model instance for every row and immediately calling save() can lead to unnecessary database round-trips if many fields are null or unchanged. Always verify that the entity has actually changed before persisting it. This small optimization drastically reduces load on the database connection and improves overall migration speed.
Another consideration is error handling within the chunk callback. Since chunk() continues processing subsequent batches even if an exception occurs in one batch, wrapping your logic inside a try-catch block ensures that a single bad record doesn't halt the entire migration. You can log the failed records to a queue or a dedicated table for manual review later.
If you find yourself needing more granular control over memory usage or processing speed, switching to cursor() is often superior:
DB::connection('legacy')
->select("SELECT * FROM companies")
->cursor()
->each(function ($company) {
$entity = Entity::firstOrNew(['external_id' => $company->companyKey]);
$entity->name = $company->companyName;
if ($entity->isDirty()) {
$entity->save();
}
});
This method streams the data row by row, keeping memory consumption constant regardless of the total dataset size. It is the most robust solution for massive legacy migrations where stability is paramount.