Eager Loading with JSON Columns in Laravel

Managing relationships among different entities has been a common requirement in the context of modern web applications. Relationships are maintained to organize and structure data in databases, facilitating efficient querying and manipulation of related data.

With this blog, I want to share a possible way to achieve our requirement to query and fetch related data on call. I faced a situation where I had to add cities for jobs, whereas cities are directly linked to prefectures. Previously, prefecture data for jobs was maintained in a separate pivot table. Now, I had to manage cities related to those prefectures selected for each job. Here, I am trying to showcase a solution to the problem with custom eager loading for cities based on JSON data, which might be helpful for you in a similar situation.

Understanding the Problem

Let's start by defining our requirements:

1. Job Table: Store information related to jobs

2. Prefecture Table (job_prefecture): Stores information for prefectures selected, including a job_id to connect with the jobs table and prefecture_id referencing to prefectures.

3. City Table: Store information related to city categorise with prefecture_id

Goal

• Our aim is to manage city data efficiently and query these relationships to eager load keeping on mind data integrity and performance.

Using JSON Column

The approach is to to maintain city Ids within job_prefectures table by introducing JSON column city_ids.

Adding a JSON Column

At first, we add city_ids JSON column on to the job_prefecture table:

Php

php artisan make:migration add_column_city_ids_on_table_job_prefecture

Add following codes in the migration files:

Php

public function up()
{
    Schema::table('job_prefecture', function(Blueprint $table)){
        $table->json('city_ids')->nullable();
    });
}

public function down()
{
    Schema::table('job_prefecture', function(Blueprint $table)){
        $table->dropColumn('city_ids');
    });
}

Define Relationships in Models

To move on , you need to define relationships in Eloquent models:

JobPrefecture.php

Php

namespace App\Models

use Illumionate\Database\Eloquent\Model;

class JobPrefecture extends Model
{
    public const TABLE_NAME = 'job_prefecture'

    protected $casts = [
        'city_ids' => 'array'
    ];

    // Custom relationship for cities
    public function cities()
    {
        $cityIds = $this->city_ids ?? [];
        return City::whereIn('id', $cityIds)->get();
    }
}

Call relationship for models

Php

namespace App\Repositories;

use App\Models\JobPrefecture;

class JobRepository
{    
    public function getJobDetail($id)
    {
        return Job::with('prefectures:id,name,job_id')->find($id);
    }

    public function getAllJobs()
    {
        return Job::with('prefectures:id,name,job_id')->get();
    }
}

Using the Repository in Controllers

Php

namespace App\Http\Controllers;

use App\Repositories\PrefectureRepository;
use Illuminate\Http\Request;

class JobController extends Controller
{
    public function __construct(protected JobRepository $jobRepository){}

    public function index(Request $request)
    {
        $jobs = $this->jobRepository->getAllJobs()
        ->map(function($job) {
            $job->prefecture->map(fn($prefecture)=>$prefecture->city)
        });    

        return response()->json($prefectures);
    }
}

References

• https://laracoding.com/how-to-search-in-a-json-column-using-laravel-eloquent/

• https://martinjoo.dev/35-eloquent-recipes

• https://planetscale.com/blog/laravels-safety-mechanisms

• https://laraveljsonapi.io/docs/3.0/resources/relationships.html

Conclusion

To wrap up, modern web applications essentially require efficient management and relationship querying. In Laravel, by using JSON columns, data storage and retrieval can be simplified, minimizing the need for complex pivot tables. This approach can be implemented to maintain data integrity, enhancing performance with minimal database queries.

This technique can be useful to maintain dynamic relationships, ensuring scalability and performant behavior of the application. Laravel's Eloquent ORM feature can be leveraged to streamline development, making it easier to handle complex relationships in a managed and efficient way.

In this blog, I've demonstrated how to manage city data linked to prefectures within job records using a custom eager loading technique. Adding a JSON column to store cities' data with the definition of custom relationships in Eloquent models allows for efficient querying and retrieval of relevant data.

I hope you found this blog helpful! Feel free to reach out if you have any questions—I’m always here to help. 😊