Laravel one to many relationship with comma separated column value

Question

In my Laravel 8 project (API), I have a model called StatusPage and a model called Monitor. A user is able to create many monitors and create a status page and choose what monitors to link to their status page, these monitors are stored in a monitors column in my StatusPage model as a comma separated list, e.g: 1,5,7,8

In my index function of my controller, I'd like to pull back a list of status pages, where each status page contains an array of monitors if there are any based on the values from my monitors column, currently, my function looks like:

/**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{

    try {

      $pages = StatusPage::where('user_id', Auth::id())->get();

      // not found
      if (!$pages) {
        return response()->json([
          'success' => false,
          'message' => 'We can\'t find any status pages'
        ], 404);
      }

      return response()->json([
        'success' => true,
        'message' => 'Your status pages',
        'pages' => $pages ?? []
      ], 200);

    } catch (\Exception $e) {

      return response()->json([
        'success' => false,
        'message' => $e
      ], 422);

    }

}

And my StatusPage model looks like:

<?php

namespace App\Models;

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

class StatusPage extends Model
{
    use HasFactory, SoftDeletes;

    /**
     * Indicates if the model's ID is auto-incrementing.
     *
     * @var bool
     */
    public $incrementing = false;

    /**
    * The table associated with the model.
    *
    * @var string
    */
    protected $table = 'status_pages';
}

How would I use One To Many in this context since this relationship expects to pull out values from a column and I'm storing the monitors in a single column?

How can I achieve this?

Answer 1

Unfortunately your relationship is not the standard one-to-many relationship handled by Laravel.

The previous answer is gonna work. However, it may not be the most performant approach. Because it execute a query for every single status page.

I got a solution that can be a bit faster, resembling how eager loading works behind the scene.

First, to make it easier to work with this monitors column. Lets create a custom cast for it.

<?php

namespace App\Casts;

use Illuminate\Contracts\Database\Eloquent\CastsAttributes;

class ArrayToString implements CastsAttributes
{
    public function get($model, $key, $value, $attributes)
    {
        return explode(',', $value);
    }

    public function set($model, $key, $value, $attributes)
    {
        return implode(',', $value);
    }
}

use App\Casts\ArrayToString;

class StatusPage extends Model
{
    protected $casts = [
        'Monitors' => ArrayToString::class,
    ];
}

Then, in your controller:

public function index()
{
    $pages = StatusPage::where('user_id', Auth::id())->get();

    // Get all relevant monitors at once.
    $monitors = $pages->map->monitors
        ->collapse()
        ->unique()
        ->pipe(function ($monitorIds) {
            return Monitor::findMany($monitorIds->toArray());
        })

    // Assign related monitors to each status page.
    $pages->each(function ($page) use ($monitors) {
        $relevantMonitors = $monitors->filter(function ($monitor) use 
            ($page) {
                return in_array($monitor->id, $page->monitor);
             });
        $page->monitors = $relevantMonitors;
    })

    return $pages;
    
}

Answer 2

From your existing schema design, I don't think you can use one to many.

There are 2 options you can try.

1. You need to alter your schema. on Monitor, you can add a column status_page_id which is a foreign key, and on model StatusPage one to many relationship to model Monitor. This is the best practice.

2. You may create a accessor (getter) on StatusPage.

// #1
public function monitors()
{
    return $this->hasMany(Monitor::class);
}

// #2
public function getMonitorsAttributes()
{
    $monitorId = $this->getOriginal('monitors');
    return Monitor::whereIn('id', explode(',', $monitorId))->get();
}

Accessor and mutator documentation is here: https://laravel.com/docs/8.x/eloquent-mutators#accessors-and-mutators