Stream large Doctrine 2 query result to output

Question

In my project I have an API call that potentially should return tens of thousands records.

The data should be returned in one chunk. No pagination is allowed by API design.

Source data is queried from MySQL using Doctrine 2 DQL and consists of several linked objects per record. At the moment the query result is around 25'000 records. I've optimised the SQL query. It runs within milliseconds so no way to optimise here.

The main problem is hydration now. I've tried different types of hydrations and it still takes too long on that amount of data. It also use too much memory.

My idea is to stream data as soon as it is hydrated and then delete data as soon as it is streamed. It will not reduce the time to complete request but it will reduce memory usage and will reduce time before response started.

Is there a way in Doctrine 2 to do some actions after each result row is hydrated?

I.e. I do the big request. I do $qb->getQuery()->getResult() and Doctrine instead of hydrating all data and returning result after each record is hydrated sends data to for example STDOUT and drop objects as soon as data is streamed.

PS: Question is not about how to stream the output of such query to HTTP. I can handle that. Question is about how I can make Doctrine 2 do what I want.

Answer 1

My solution (including a CSV streaming for completeness):

function getExportableHead()
{
    // returns array of fields for headings
}

function getExportableRow($row)
{
    // returns array of values for a row
}

$qb = $this->em->getRepository(Item::class)->createSomeQueryBuilder();
$response = new StreamedResponse(function () use ($qb) {
    $data = $qb->getQuery()->iterate();
    $handle = fopen('php://output', 'w+');
    fputcsv($handle, getExportableHead(), ';');
    while (($object = $data->next()) !== false) {
        $row = getExportableRow($object[0]);
        fputcsv($handle, $row, ';');
        $this->em->detach($object[0]);
    }
    fclose($handle);
});
$response->headers->set('Content-Type', 'text/csv; charset=utf-8');
$response->headers->set('Content-Disposition', 'attachment; filename="out.csv"');