Laravel Excel Export with HasOne Relationship

Do you want to export Excel with relationship data in the Laravel application?

This step-by-step tutorial helps you learn how to export Excel with HasOne Relationship in Laravel application with the help of the maatwebsite Excel package.

Need to Excel export with relationship data in Laravel

For web applications, exporting data from the database into an Excel sheet is an important feature. Because of this feature, we can easily perform batch export data by the web application. It is difficult in other Frameworks but easiest in Laravel. It gives maatwebsite/excel package to easily export data.

Laravel Excel Features

Easily export collections to Excel.
Export queries with automatic chunking for better performance.
Queue exports for better performance.
Easily export Blade views to Excel.
Easily import to collections.
Read the Excel file in chunks.
Handle the import inserts in batches.

Okay, let’s get straight to the steps for the export Excel with relationship data in Laravel with an Example 👇

Generate migration with model

php artisan make:model Category -m

php artisan make:model Post -m

database/migrations/create_categories_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('categories', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->string('name');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('categories');
    }
};

database/migrations/create_posts_table.php

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('posts', function (Blueprint $table) {
            $table->bigIncrements('id');
            $table->unsignedBigInteger('cat_id');
            $table->string('title');
            $table->longText('content');
            $table->timestamps();
            $table->foreign('cat_id')
            ->references('id')
            ->on('categories')
            ->onDelete('cascade');
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts');
    }
};

Next, you can migrate table properties in the database with the artisan migrate command:

php artisan migrate

Laravel Excel Installation

composer require maatwebsite/excel

To publish the config, run the vendor publish command:

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider" --tag=config

app\Models\Category.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Category extends Model
{
    use HasFactory;

}

app\Models\Post.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    use HasFactory;

    public function categoryData()
    {
        return $this->hasOne('App\Models\Category','id','cat_id');
    }
}

Create a Controller

php artisan make:controller PostExport

routes\web.php

<?php

use Illuminate\Support\Facades\Route;
use App\Http\Controllers\PostController;


Route::get('/list-post',[PostController::class,'listPost'])->name('list.post');
Route::get('/export-post',[PostController::class,'exportPost'])->name('export.post');

app\Http\Controllers\PostController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Post;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\CategoryExport;

class PostController extends Controller
{
    public function listPost()
    {
        $posts = Post::with('categoryData')->latest()->get(); 
        return view('post.list',['posts' => $posts]);
    }

    public function exportPost()
    {
        return Excel::download(new PostExport, 'posts.xlsx');
    }
}

app\Exports\PostExport.php

<?php

namespace App\Exports;

use App\Models\Post;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\Exportable;

class PostExport implements FromCollection,WithMapping,WithHeadings
{
    /**
    * @return \Illuminate\Support\Collection
    */
    public function collection()
    {
        return Post::with('categoryData')->latest()->get();
    }

    public function map($post) : array {
        return [
            $post->title,
            $post->categoryData['name'],
            $post->content,
        ];
    }

    public function headings(): array
    {
        return [
            'Title',
            'Category',
            'Content',
        ];
    }
}

resources\views\post\list.blade.php

<!DOCTYPE html>
<html lang="en">
   <head>
      <meta charset="utf-8">
      <meta name="viewport" content="width=device-width, initial-scale=1">
      <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
      <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
   </head>
   <body>
   <div class="mt-5">
   <div class="d-grid gap-2 d-md-block">
   <a href="{{ route('export.post') }}"><button type="button">Export Data</button></a>
   </div>
    </div>
      <div class="container mt-5">
         <h3>Export Post Table with Category</h3>
         <table class="table">
            <thead>
               <tr>
                  <th>Title</th>
                  <th>Category</th>
                  <th>Content</th>
               </tr>
            </thead>
            @forelse ($posts as $post)
            <tbody>
               <tr>
                  <td>{{ $post->title }}</td>
                  <td><span class="badge bg-success">{{ $post->categoryData['name'] }}</span></td>
                  <td>{{ $post->content }}</td>
               </tr>
            </tbody>
            @empty
            <p>No Post Found!</p>
            @endforelse
         </table>
      </div>
   </body>
</html>

Run application:

http://127.0.0.1:8000/list-post

Output:

I hope that this article helped you learn an example of the laravel excel export with hasOne relationship in Laravel 9, with the help of the maatwebsite example. You may also want to check out our guide on How to set limits on login attempts in laravel 9 example in the Laravel application.