-1

I'v staked with a one thing in laravel. I have huge database with those kinds of tables: products, categories and regions.

Products

  • id
  • category_id
  • region_id

Categories

  • id
  • parent_id = category.id or 0 if it is root

Region

  • id
  • parent_id = region.id or 0 if it is root

And I have to get all root regions who connected to specific category. The only solution I see it's to do it like this

$products = Category::products->all();
$rootCategories = [];
foreach($products as $product){
 $rootCategories[] = $product->region->ultimateParent(); //Region::ultimateParent();
}

What do you think is there some kind of more eloquent way to solve it?

Andrew Tregubov
  • 1
  • 2
  • There's no good relational way to solve this other than going up a parent until you hit a root. This can be optimised by storing the root_parent_id in each field but beware that this means you have to denormalise your data. – apokryfos Nov 23 '18 at 13:53
  • Possible duplicate of [How to create a MySQL hierarchical recursive query](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) – apokryfos Nov 23 '18 at 14:00

1 Answers1

0

you have to use join like:

$query=DB::table('products')
       ->join('categorie','products.catagory_id','=','catagories.id')
       ->join('region','products.region_id','=','region.id')
       ->select('products.*','catagories.*',region.*'
       ->get();

This query will give you all the fields from all 3 tables.

Sandy von
  • 57
  • 8