Using Laravel Resource Collections with exports

Published: Apr 11, 2021 by C.S. Rhymes

Sometimes you need to get data out of your Laravel app so it can be analysed. There are many ways of doing this, such as exporting rows from the database, or using a package such as Laravel Excel, but this example will utilise Laravel Resource Collections.

In this example we are going to create an export of User information into Excel using Laravel Resource Collection and Spatie’s simple excel package. Laravel Resources are normally used to transform your data for use in an API, but you can also create an array from the resource for use in our export.

The users table has been updated so a user belongs to a company. The companies table has a name for the company in this example.

// App/Models/User.php

public function company()
{
    return $this->belongsTo(\App\Models\Company::class, 'company_id', 'id');
}

Creating the User Resource

To create the user resource we can use the artisan command:

php artisan make:resource UserResource

We can then specify the data we want to include for each user. We will add the name, email and the company name.

<?php

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;

class UserResource extends JsonResource
{
    /**
     * Transform the resource into an array.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return array
     */
    public function toArray($request)
    {
        return [
           'name' => $this->name,
           'email' => $this->email,
           'company' => $this->company->name,
        ];
    }
}

Converting the User Resource to an array

When you return the UserResource it will provide you with a json response with the users within the data key. This is perfect for use with an API.

return UserResource::collection(User::get());

Each user is listed with their name, email and company. The keys are what we defined in the UserResource toArray() method so if we wanted to add additional data, for example such as a company phone number that is added in future, then we can update the UserResource and the results will be updated accordingly.

{
    "data": [
        {
            "name": "Zechariah Olson DDS",
            "email": "dreynolds@example.net",
            "company": "Boyle Ltd"
        }
    ]
}

We can modify this statement slightly to provide an array of just the user data we need without the data key by using the resolve() method.

return UserResource::collection(User::get())->resolve();

We can also update the query from User::get() to User::cursor() to return a lazy collection and help reduce the amount of memory when we have many users to export. Cursor allows us to only load the current model we are iterating over, rather than loading all models at once.

return UserResource::collection(User::cursor())->resolve();

Exporting to Excel

Now we are ready to make use of the Simple Excel package from Spatie to convert our array into an Excel file.

composer require spatie/simple-excel

The simple excel package provides a writer to help us easily create an excel file using SimpleExcelWriter::create() but since the file could be very large if our Laravel app has numerous users we can use the SimpleExcelWriter::streamDownload() method instead to allow us to stream the download directly to the browser.

Once we have our writer instance we can add rows of data to the file using the ->addRows() method which expects an array. This is where we pass in our UserResource, with the resolve method so it is converted into an array.

Finally we add ->toBrowser() to return the response to the browser and allow the file to download.

// App/Controllers/UserController.php

public function export()
{
    return SimpleExcelWriter::streamDownload('users-export.xlsx')
        ->addRows(
            UserResource::collection(User::cursor())
                ->resolve()
        )
        ->toBrowser();
}

Other possibilities

Once you have created the resource you can reuse it as it was originally intended as an API response, but you could also use it with another Spatie package called array to xml to convert your array into an XML file instead of a CSV or Excel file.

Photo by Matt Bango from StockSnap

Laravel Exports Resource Collections

Share

Latest Posts

Creating multi-worksheet Excel files with Simple Excel
Creating multi-worksheet Excel files with Simple Excel

Recently I had to create a large data export for a project. I like using Spatie’s Simple Excel package to do this as it is very simple to use and works well when exporting large amounts of data to a CSV or Excel file with the ability to stream a download to the browser. This particular project had an additional requirement though, exporting multiple worksheet’s of data at once. Luckily, this package allows you to do this too.

The PHP foreach loop
The PHP foreach loop

An alternative to the for loop, the foreach loop is used to iterate or loop over an array. The foreach loop allows you to loop through the items in the array without setting a limit for it to stop like you do in a for loop.

The PHP for loop
The PHP for loop

If you have something you want to repeat then consider using a PHP for loop, saving you from copying and pasting the same code multiple times. Adding the code into a loop means you only have to write it once, and you also only have to maintain the code in one place in future.

How NOT to make a website

How NOT to make a Website

By C.S. Rhymes

From £8.99

Nigel's Intranet Adventure

Nigel's Intranet Adventure

By C.S. Rhymes

From £2.69