# Best way to export large ResultSet

**URL:** <https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592>\
**Category:** Need Help\
**Created:** [January 30, 2019, 8:52pm UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592 "2019-01-30T20:52:41Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![okelet](https://avatars.discourse-cdn.com/v4/letter/o/919ad9/32.png) [@okelet](https://discourse.cakephp.org/u/okelet)\
**Post date:** [January 30, 2019, 8:52pm UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592/1 "2019-01-30T20:52:42Z")

</div>

Hi all, we have an option in our application to export data. The table has a lot of information, and also relations (that I need to export, for example, for a user, I want to export the company name he belongs to). The resultset is about 50,000 records. I have tried many workarounds: disable query buffer (`$query->enableBufferedResults(false);`, that throws an error beause relations: Cannot execute queries while other unbuffered queries are active), use `$q->each`, etc. without success, I always get a memory exhausted error (with 256M); I wouldn’t like to increase PHP memory, but find a better way to do this. Any idea? Thanks! (CakePHP 3.6, PHP 7.2, Ubuntu 18.04 x64)

---

<div class="post-metadata">

**Author:** ![jimgwhit](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/jimgwhit/32/378_2.png) [@jimgwhit](https://discourse.cakephp.org/u/jimgwhit)\
**Post date:** [January 31, 2019, 3:56am UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592/2 "2019-01-31T03:56:35Z")

</div>

Chunk them, Id 1 - 1000, then the next ID that’s higher than 1000 -2000.

Write a small routine and use some if statements with some less than and greater than statements.

---

<div class="post-metadata">

**Author:** ![jarekgol](https://avatars.discourse-cdn.com/v4/letter/j/58956e/32.png) [@jarekgol](https://discourse.cakephp.org/u/jarekgol)\
**Post date:** [January 31, 2019, 9:10pm UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592/3 "2019-01-31T21:10:40Z")

</div>

or instead of manual id compare use sql/cake limit or try paginator and write limited data to file in loop.  
above of [https://book.cakephp.org/3.0/en/orm/query-builder.html#selecting-specific-fields](https://book.cakephp.org/3.0/en/orm/query-builder.html#selecting-specific-fields)

---

<div class="post-metadata">

**Author:** ![MarceliSzpak](https://avatars.discourse-cdn.com/v4/letter/m/4af34b/32.png) [@MarceliSzpak](https://discourse.cakephp.org/u/MarceliSzpak)\
**Post date:** [February 1, 2019, 12:48pm UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592/4 "2019-02-01T12:48:46Z")

</div>

I would just paginate query and increase php execution time.  
You can do it directly in model like:

```
$cnt = $yourQuery->count();
$recordsLimit = 1000;
$pages = ceil($cnt / $recordsLimit);

 for( $page = 1; $page <= $pages; $page++) {
    $yourQuery->limit($limit)->page($page);
    $result = $query->toArray();
 }
```

---

<div class="post-metadata">

**Author:** ![dmuenstermann](https://avatars.discourse-cdn.com/v4/letter/d/b9bd4f/32.png) [@dmuenstermann](https://discourse.cakephp.org/u/dmuenstermann)\
**Post date:** [February 4, 2019, 10:50am UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592/5 "2019-02-04T10:50:45Z")

</div>

I’d also try to **limit the related data** to only the fields you really need - like this:

```
$users = $this->Users->find()
    ->select(['id', 'name', 'customer_alias' => 'Customers.alias', 'certificate'])
    ->contain([
	'Roles' => [
		'fields' => ['Roles.name', 'RolesUsers.user_id']
	],
	'Customers'
    ])
    ->all()
;

```

This can drastically shrink the fetched result size and therefore the used memory.

---

<div class="post-metadata">

**Author:** ![okelet](https://avatars.discourse-cdn.com/v4/letter/o/919ad9/32.png) [@okelet](https://discourse.cakephp.org/u/okelet)\
**Post date:** [February 4, 2019, 11:14am UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592/6 "2019-02-04T11:14:46Z")

</div>

Thanks all for your tips. I’ll try to implement these workarounds and give you some feedback.

---

<div class="post-metadata">

**Author:** ![raul338](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/raul338/32/69_2.png) [@raul338](https://discourse.cakephp.org/u/raul338)\
**Post date:** [February 4, 2019, 1:43pm UTC](https://discourse.cakephp.org/t/best-way-to-export-large-resultset/5592/7 "2019-02-04T13:43:12Z")

</div>

You’re likely at some point to hit a timeout (php, apache/nginx, or the user browser).

The best way is to trigger a task, make the export via cli and alert the user that the task is finished, with a link to the generated export.

you can use queues, eg: [cakephp-queue](https://github.com/dereuromark/cakephp-queue)