Laravel Relationship with Comma Separated Values

In this post, I’ll show you how to set up relationships with comma-separated column values in a Laravel application. Sometimes, we store IDs in a column as comma-separated values, but when it comes to fetching related data from another table, establishing a relationship can be tricky. That’s where the ghanuz/relations package comes in handy.

To explain this, I’ll create two tables: products and colors. When adding a product, we’ll store the associated color IDs in a comma-separated column.

Then, I’ll demonstrate how to use Laravel relationships to retrieve the color details effortlessly. This approach is perfect for handling legacy databases or unconventional data structures in Laravel projects.

In this step, we'll install laravel 11 application using the following command.

composer create-project laravel/laravel example-app

Then, we'll install composer package using the following command.

composer require ghanuz/relations

Next, we'll create migration using the following command.

php artisan make:migration create_products_colors_table

Migration:

<?php

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

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

        Schema::create('colors', function (Blueprint $table) {
            $table->id();
            $table->string("name");
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('products');
        Schema::dropIfExists('colors');
    }
};

Now, migrate the table into the database using the following command.

php artisan migrate

Next, we'll create a model using the following command.

php artisan make:model Product

php artisan make:model Color

We will use FindInSetMany() method for colors relationship.

app/Models/Product.php

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use GhanuZ\FindInSet\FindInSetRelationTrait;

class Product extends Model
{
    use HasFactory, FindInSetRelationTrait;

    protected $fillable = ["name", "color"];

    /**
     * Write code on Method
     *
     * @return response()
     */
    public function colors()
    {
        return $this->FindInSetMany( Color::class, 'color', 'id');
    }
}

app/Models/Color.php

<?php

namespace App\Models;

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

class Color extends Model
{
    use HasFactory;

    protected $fillable = ["name"];
}

Next, we'll define the routes into the web.php file.

routes/web.php

<?php

use Illuminate\Support\Facades\Route;

Route::get('/', function () {
    return view('welcome');
});

Route::get('/products', [App\Http\Controllers\ProductController::class, 'index']);

Then, create a controller using the following command.

php artisan make:controller ProductController

app/Http/Controllers/ProductController.php

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\Product;
use App\Models\Color;

class ProductController extends Controller
{
    /**
     * Write code on Method
     *
     * @return response()
     */
    public function index()
    {
        $red = Color::create(["name" => "Red"]);
        $white = Color::create(["name" => "White"]);
        $blue = Color::create(["name" => "Blue"]);

        $gold = Product::create(["name" => "Gold", "color" => "{$red->id},{$white->id}"]);
        $silver = Product::create(["name" => "Silver", "color" => "{$red->id},{$blue->id}"]);

        print_r("GOLD:");
        print_r($gold->toArray());
        print_r($gold->colors->toArray());
        print_r("SILVER:");
        print_r($silver->toArray());
        print_r($silver->colors->toArray());
    }
}

Now, run the laravel application using the following command.

php artisan serve

Output:

GOLD:
Array
(
    [name] => Gold
    [color] => 1,2
    [updated_at] => 2024-11-30T11:27:30.000000Z
    [created_at] => 2024-11-30T11:27:30.000000Z
    [id] => 1
)
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Red
            [created_at] => 2024-11-30T11:27:30.000000Z
            [updated_at] => 2024-11-30T11:27:30.000000Z
        )

    [1] => Array
        (
            [id] => 2
            [name] => White
            [created_at] => 2024-11-30T11:27:30.000000Z
            [updated_at] => 2024-11-30T11:27:30.000000Z
        )

)
SILVER:
Array
(
    [name] => Silver
    [color] => 1,3
    [updated_at] => 2024-11-30T11:27:30.000000Z
    [created_at] => 2024-11-30T11:27:30.000000Z
    [id] => 2
)
Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Red
            [created_at] => 2024-11-30T11:27:30.000000Z
            [updated_at] => 2024-11-30T11:27:30.000000Z
        )

    [1] => Array
        (
            [id] => 3
            [name] => Blue
            [created_at] => 2024-11-30T11:27:30.000000Z
            [updated_at] => 2024-11-30T11:27:30.000000Z
        )

)

You might also like:

• Read Also: Laravel Relationship with JSON Column
• Read Also: Laravel 11 Spatie Media Library Example
• Read Also: How to Search Comma Separated Values in Laravel
• Read Also: Feature Flag System in Laravel 11 for Controlled Releases