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.

Solution 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.

Solution 2

You can try this way:

// Category.php
public function children()
{
    return $this->hasMany(Category::class, 'parent_id');
}

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

// To access
$category->children; // sub-categories collection

$category->parent; // parent instance

based on : https://laracasts.com/discuss/channels/eloquent/self-join-in-eloquent-model?page=1

Solution 3

You might want to do as follow :

$id = 1;
$product = Product::with('children')->whereHas('children', function($q) use ($id)
{
    $q->where('color_id', $id);
})->find($id);

Solution 4

See Advanced Join Clauses and adapt from my example here.

It took me a long time to wrap my head around Laravel joins.

This:

$postIds = DB::table('comments as t1')
     ->select('t1.*')
            ->leftJoin('comments as t2', function ($join) {
                $join->on('t1.postId', '=', 't2.postId')
                ->on('t1.created_at', '<', 't2.created_at');
            })
            ->where('t2.id', '=', null)
            ->orderBy('t1.created_at', 'DESC')
            ->simplePaginate(20)
            ->pluck('postId');

seems to be the way to get an array of the postIds that would be revealed by this:

SELECT     t1.* 
FROM     comments a         
LEFT OUTER JOIN     comments b
 ON t1.postId = t2.postId        
 AND t1.created_at < t2.created_at 
WHERE     t2.id IS NULL     
ORDER BY t1.created_at DESC

View more solutions

Comments

[panthro]

Thanks but my original query would be more efficient.

[MaGnetas]

Then use actual query builder methods. Eloquent does not do joins.

[omarjebari]

Eloquent DOES do joins