Self Join in Eloquent

Question

How would you write a self join in eloquent? Would I need to define the relationship on the model?

Here's my statement:

SELECT t2.title FROM products t1, products t2
WHERE t1.id = $id 
AND t2.color_id = t1.color_id AND
t2.id != $id

Hominy

Answer 1

You can simply define a relation to itself.

public function parent()
{
    return $this->belongsTo(self::class, 'color_id');
}

public function children()
{
    return $this->hasMany(self::class, 'color_id');
}

Terbium

Thanks, how would I call this? I've tried Product::with('children')->find(1) but this comes back with all of the children with a color id of 1, I need to get out row 1 from products and the children should be the color id that is on row 1. – Hominy