CODEWITHSUNDEEP
phpsqllaraveleloquent
stackoverflow account
stackoverflow account

Reputation: 235

Laravel : Eloquent query for self JOIN

I have a categories table where categories have a parent mentioned within same table as in below screenshot

enter image description here

I want to implement self join in Laravel using Eloquent where parent category is mentioned in a saperate col:

Raw Sql Query:

DB::select("SELECT  c.id, c.name, parents.name AS `Parent Category Name`
FROM  product_categories AS c
LEFT  JOIN  product_categories AS parents ON parents.id = c.parent
ORDER BY   c.name ASC");

which gives me following result :

array:3 [
  0 => {#1146 
    +"id": 1
    +"name": "category 1"
    +"Parent Category Name": "category 2"
  }
  1 => {#673 
    +"id": 2
    +"name": "category 2"
    +"Parent Category Name": null
  }
  2 => {#1079
    +"id": 3
    +"name": "category 3"
    +"Parent Category Name": null
  }
]

i want to achieve the same using Laravel Eloquent and then loop in my view , can someone please help me i have tried multiple variations found from my searches which did not work

Upvotes: 0

Views: 1081

Answers (1)

Atika
Atika

Reputation: 1067

Try something like :

$result = ProductCategorie::leftJoin('product_categories as parents', 'parents.id', '=', 'product_categories.parent')
         ->select('product_categories.id', 'product_categories.name', 'parents.name as ParentCategoryName')
         ->get();

Upvotes: 1

Related Questions