Self Join in Eloquent - How To Call

Question

I recently asked a question regarding a self join

SO

I got a great answer but I'm not sure how to call it.

I need to do this query:

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

I now have this on my products model:

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

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

But how do I call this?

Product::with('children')->find(1);

The above gets the product with id 1 but also gets children that have a color_id of 1, I need to get children who have a color_id the same as the id as product 1.

eg.

Products

id | title | color_id
 1    dvd       2

When I select row 1 it should select all other products with a color_id of 2.

Answer 1

I believe your relations are not the way they're supposed to be. Usually it's one column (foreign key - color_id in your case) having a value of the other one (usually primary key - id in your case).

What you have is basically a value the records share or a "category". So your products are not "children" but rather siblings (have the same parent color).

Since with method is not build as a JOIN statement but as eager loading (separate query) you can do that manually.

Probably the most straight forward way:

$product1 = Product::find(1);
$children = Product::where('id', '<>', $product1->id)->
    where('color_id', $product1->color_id)->
    get();

You can add select('title') to the second "builder" to get only title but that would not be your model anymore. Or you can use lists('title') to extract only titles if that's what you need.

UPDATE:

If you decide you need the JOIN after all I'd suggest going with raw query builder and leave the Eloquent out of it:

$res = DB::table('products as t1')->
    select('t2.title')->
    join('products AS t2', 't2.color_id', '=', 't1.color_id')->
    where('t1.id', 1)->
    where('td2.id', '<>', 't1.id')->
    get();

I believe it should build something similar to what you need.