Home > Mobile >  How to properly retrieve and store self referencing table relationship in Laravel?
How to properly retrieve and store self referencing table relationship in Laravel?

Time:09-24

Hi i'm just started to learn laravel and i have 2 database tables that i want to modify, the first one is the main table which is products and the other table is upsells which will connect two entity from products table:

products

id name price
1 Bag 300
2 Belt 100
3 ring 120
4 Hat 250
5 Scarf 125

upsells

id product_id_1 product_id_2
1 2 1
2 2 4
3 2 5
4 5 4
5 5 1

the idea is product enitty can be connected to the one or more product using the upsells table. the product_id_1 will be the main product that will point to the product_id_2

Currently i'm using this method to retrieve the relationship:

public function getUpsells($mainProductId){
  $upsells = Upsell::where('product_id_1', $mainProductId);
  $results = array();
  foreach($upsells as $upsell){
    $results[] = $upsell->product_id_2;
  }
  return $results;
}

$mainProductId = 5;
$relatedProducts = array(2,3);
public function updateUpsells($mainProductId,$relatedProducts){
  foreach($relatedProducts as $relatedProduct){
    //create if not exists
    $upsell = Upsell::where('product_id_1', $mainProductId)->
                    where('product_id_2', $relatedProduct)->first();
    if(empty($upsell->id)){
      $upsell = new Upsell;
      $upsell->product_id_1 = $mainProductId;
      $upsell->product_id_2 = $relatedProduct;
    }
  }
  //delete if not in the array input
  Upsell::where('product_id_1', $mainProductId)->
                    whereNotIn('product_id_2', $relatedProducts)->delete();
  
}

Is there any simplified method or any method that i'm missing based on laravel best practice ?

CodePudding user response:

You can use eloquent relationships. Based on your code I think you have these relationships between your models:

Products hasMany upSells,

so in your Products.php you will have to add relation like this:

public function upSells(){
return $this->hasMany(UpSells::class, 'product_id_1', 'id');
}

This way you can fetch upsells from products like this:

$products = Product::with('upSells')->find(1);

The same thing you can do with your other relationships. You just have to define which will be the parent and which will be the child in the relationship.

For more information, you can go through this.

CodePudding user response:

CURRENT SOLUTION WHICH IS NOT RECOMMENDED

public function getUpsells($mainProductId)
{
  return Upsell::where('product_id_1', $mainProductId)->pluck('product_id_2'); //This will give you array of product_id_2.
}

$mainProductId = 5;
$relatedProducts = [2, 3];
public function updateUpsells($mainProductId, $relatedProducts)
{
    Upsell::where('product_id_1', $mainProductId)->delete();

    foreach($relatedProducts as $relatedProduct){
        $upsells = [
            [
                'product_id_1' => $mainProductId,
                'product_id_2' => $relatedProduct
            ]
        ];
    }

    Upsell::insert($upsells);
}

RECOMMENDED SOLUTION

Important docs,

https://laravel.com/docs/8.x/eloquent-relationships

https://laravel.com/docs/8.x/migrations

You have to make a relation in the product model make migration of pivot table.

public function upsells() 
{
    return $this->hasMany(Upsell::class, 'product_id_1', 'id')
}

then getUpsells() and updateUpsells() be like,

public function getUpsells($mainProductId)
{
    $products = Product::with('upsells')->findOrFail($mainProductId);

    return $products->upsells->product_id_2;
}

$mainProductId = 5;
$relatedProducts = [2, 3];
public function updateUpsells($mainProductId, $relatedProducts) 
{
    $product = Product::findOrFail($mainProductId);

    $product->upsells()->sync($relatedProducts);
}
  • Related