{"id":200,"date":"2023-09-26T15:27:19","date_gmt":"2023-09-26T14:27:19","guid":{"rendered":"https:\/\/mrzebra.co.uk\/code\/?p=200"},"modified":"2023-09-26T15:32:32","modified_gmt":"2023-09-26T14:32:32","slug":"how-to-perform-an-unbuffered-query-in-laravel","status":"publish","type":"post","link":"https:\/\/zebra-north.com\/code\/2023\/09\/26\/how-to-perform-an-unbuffered-query-in-laravel\/","title":{"rendered":"How to Perform an Unbuffered Query in Laravel"},"content":{"rendered":"\n

If you wish to process a very large number of rows from a database then it may be prohibitively expensive to load them all into memory first. Laravel’s chunk()<\/code> function may look like the solution however this performs a query for each chunk and is therefore extremely slow.<\/p>\n\n\n\n

The ideal solution is to disable query buffering and thereby only ever have a single row in memory at once. Note that this option is specific to the MySQL PDO driver.<\/p>\n\n\n\n\n\n\n\n

\/\/ Disable query buffering on the connection.\n$connection = DB::connection();\n$pdo = $connection->getReadPdo();\n$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);\n\n\/\/ Build your query.\n$query = DB::table('users')->select('*')->orderBy('name');\n\n\/\/ Execute the query and iterate over each row.\nforeach ($connection->cursor($query->toSql(), $query->getBindings()) as $row) {\n    var_dump($row);\n}\n\n\/\/ Re-enable query buffering.\n$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);<\/code><\/pre>\n\n\n\n
This code first fetches the PDO connection to the database and disables query buffering.  You can use Laravel’s query builder as normal to construct your query.  The connection’s cursor()<\/code> function is used to iterate over every row.<\/p>\n\n\n\n
Note that you must re-enable query buffering after you have finished, or later queries will be affected.<\/p>\n\n\n\n
Unbuffered HTTP Output<\/h2>\n\n\n\n
If you wish to return the data from an HTTP endpoint, for example as a data feed, then you must also make sure that the output is not buffered.  This is accomplished by using the stream()<\/code> or streamDownload()<\/code> method on the response.<\/p>\n\n\n\n
streamDownload()<\/code> takes four parameters:<\/p>\n\n\n\n
    \n
  1. A callback that produces the page output via echo\/print\/etc.<\/li>\n\n\n\n
  2. The filename, which defaults to the route name if null<\/code>.<\/li>\n\n\n\n
  3. Additional HTTP headers, used here to set the Content-Type<\/code>.<\/li>\n\n\n\n
  4. The HTTP disposition, which defaults to attachment<\/code>.<\/li>\n<\/ol>\n\n\n\n
    The example below shows how to add CSV output to your controller class.<\/p>\n\n\n\n
    use Illuminate\\Http\\Request;\nuse Symfony\\Component\\HttpFoundation\\StreamedResponse;\n\nclass MyApiController extends Controller\n{\n    public function csv(Request $request): StreamedResponse\n    {\n        return response()->streamDownload([$this, 'outputCsv'], null, ['Content-Type' => 'text\/csv']);\n    }\n\n    private function outputCsv(): void\n    {\n        \/\/ Disable query buffering on the connection.\n        $connection = DB::connection();\n        $pdo = $connection->getReadPdo();\n        $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);\n\n        \/\/ Build your query.\n        $query = DB::table('users')->select('name, email')->orderBy('name');\n\n        \/\/ Execute the query and iterate over each row.\n        foreach ($connection->cursor($query->toSql(), $query->getBindings()) as $row) {\n            echo $row->name . ',' . $row->email. "\\n";\n        }\n\n        \/\/ Re-enable query buffering.\n        $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);    }\n}<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
    If you wish to process a very large number of rows from a database then it may be prohibitively expensive to load them all into memory first. Laravel’s chunk() function may look like the solution however this performs a query for each chunk and is therefore extremely slow. The ideal solution is to disable query […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[11],"class_list":["post-200","post","type-post","status-publish","format-standard","hentry","category-php","tag-laravel"],"_links":{"self":[{"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/posts\/200","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/comments?post=200"}],"version-history":[{"count":3,"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/posts\/200\/revisions"}],"predecessor-version":[{"id":203,"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/posts\/200\/revisions\/203"}],"wp:attachment":[{"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/media?parent=200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/categories?post=200"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zebra-north.com\/code\/wp-json\/wp\/v2\/tags?post=200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}