CODEWITHSUNDEEP
phpeloquentpivot-tablerelationshiplaravel-8
Raajen
Raajen

Reputation: 175

Laravel PIVOT table with extra fields

I've the following structure in my database.

users
--
id
...

products
--
id
...

licenses (pivot table with some extra informations these data are grabbed via API)
--
user_id
product_id
license_key
license_type
url
purchased_at (datetime)
supported_until (datetime)
...

And here are codes in my model:

# User has many licenses
# User has many products through licenses
User
  // User has many licenses.
  public function licenses()
  {
    return $this->hasMany(License::class);
  }
  
  // User has many products
  // Expected: products
  // Outcome: not getting
  public function products()
  {
     return $this->belongsToMany(Product::class, 'licenses', 'user_id', 'product_id')
            ->using(License::class);
  }

Product Model.

# Product has many licenses
Product
  
  public function licenses()
  {
    return $this-hasMany(License::class);
  }

License Model

# License belongs to an user.
# License belongs to a product.
License

  public function user()
  {
    return $this->belongsTo(User::class);
  }
  
  public function product()
  {
    return $this->belongsTo(Product::class);
  }

Route

// These class namespaces are imported and all the necessary middlewares are applied. Route::get('/my-products', [ProductController::class, 'index']);

And in product controller

ProductController

  public function index()
  {
    $user = Auth::user();
    
    // This doesn't get any products. Which is what I want to use.
    $products = $user->products;
    
    // This works but I want to display more efficiently and using less DB query. 
    $licenses = $user->licenses;
    $products = [];
    foreach ($licenses as $license) {
        array_push( $products, $license->product)
    }
    
    // Get unique products of an user through licenses relation.
    $products = collect($products)->unique();
  }

I'm not getting any products from the licenses relation when using $user->products I want to paginate the products if there are more than 5 products in a page and reduce extra layer of database queries and model loading. Please suggest some better way.

Upvotes: 1

Views: 516

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

I would suggest to perform a separate single query for products that belongs to user via license as

$products = Product::whereHas('licenses.user', function (Builder $query) use($user) {
                       $query->where('id', $user->id);
                     })->paginate(5);

or

$products = Product::whereHas('licenses', function (Builder $query) use($user) {
                       $query->where('user_id', $user->id);
                     })->paginate(5);

This way you don't have to loop through all data to extract products from lazy loaded relation and no need for unique action also

Upvotes: 1

Related Questions