Getting parent parents data in a pivot table in Laravel

Question

What Eloquent model would you use to:

Get all Pages that its Company brand color is red/slug.

Where brand color is a pivot table and stored as an ID on the Company table.

So you have brand_colors id, name, slug, hex Have company where it has brand_color_id And Pages that has just the company ID.

I want to grab all pages whose company brand color is X.

How would you go about it?

This table has the company_id

class WebsitePage extends Model
{
    use HasFactory;

    public function scopeFilter($query, array $filters) {

        // Get all pages with a color that the company has
        $query->when($filters['color'] ?? false, fn($query, $color) =>
        $query->whereHas('color', fn ($query) =>
                $query->where('name', $color)
            )
        );

    }

    // Where URL slug 'red' color, associated with company brand_color_id matches brandColor slug
    // or just simpley call $company->color() ?
    public function color()
    {
        // return $this->with(Company::class, 'brand_color_id');
        // return $this->belongsTo(Company::class, 'company_id');
        // dd($this->with())

        // return Company::with('color.red');
    }

This table has brand_color_id, etc.

class Company extends Model
{
    use HasFactory;

    protected $fillable = [
        'user_id',
        'name',
        'slug',
        'description'
    ];


    public function pages() {
        return $this->hasMany(WebsitePage::class);
    }

    public function color() {
        return $this->belongsTo(BrandColor::class, 'brand_color_id');
    }
}

This table has: id | name | slug | hex

class BrandColor extends Model
{
    use HasFactory;
}

So knowing this, how would you get all pages whose company color is the one filtered?

In terms of the page controller, this is what I'm doing:

class PageController extends Controller
{
    public function index(WebsitePage $websitePage)
    {
        // return view('web.inspiration.pages.index', [
        //     'pages' => WebsitePage::latest()->paginate(30)
        // ]);
        return view('web.inspiration.pages.index', [
            'pages' => WebsitePage::latest()->filter(
                request(['color'])
            )->paginate(30)->withQueryString()
        ]);
    }
}

Answer 1

Your model layout isn't described that well, but I think you just want to define a HasOneThrough relationship to join WebsitePage to BrandColor:

class WebsitePage extends Model
{
    public function company(): BelongsTo
    {
        return $this->belongsTo(Company::class);
    }

    public function color(): HasOneThrough
    {
        return $this->hasOneThrough(BrandColor::class, Company::class);
    }
}

class Company extends Model
{
    public function color(): BelongsTo
    {
        return $this->belongsTo(BrandColor::class);
    }
}

class BrandColor extends Model
{
    public function company(): HasOne
    {
        return $this->hasOne(Company::class);
    }
}

Then in your controller, just use a condition on your whereHas() and with() calls:

class PageController extends Controller
{
    public function index(): View
    {
        $color = request("color");
        $pages = WebsitePage
            ::whereHas("color", fn ($q) => $q->where("color", $color))
            ->with(["color" => fn ($q) => $q->where("color", $color)])
            ->paginate(30)
            ->withQueryString();
        return view("web.inspiration.pages.index", compact($pages));