Laravel Eloquent Nested Query
I was working with Laravel and got stuck in the situation. I have the following models:
- Category
- Product
- CategoryProduct
CategoryProduct
contains information about which product belongs to which category (a product can belong to several categories).
Now when I want to download all the products belonging to a specific category, I need to run a query on Product
and CategoryProduct
where I am stuck.
I tried the following attempt but was unsuccessful:
$products = Product::where('status', '=', 'active')
->where('category_id', '=', $category_id)
->take($count)
->skip($skip)
->get();
Obviously he will say he is category_id
not a column.
Here is my DB structure and Model:
category table
ID, name, etc.
product table
ID, name, s, etc.
category_products table
ID, product_id, (foreign key to Product.id) category_id, (foreign key to Category.id), etc.
Product model
class Product extends Eloquent {
protected $table = 'products';
protected $hidden = array();
public static $rules = array('name' => 'required|min:3');
}
Category model
class Category extends Eloquent {
protected $table = 'categories';
public static $rules = array('name' => 'required|min:3');
}
Product category
<?php
class CategoryProduct extends Eloquent {
protected $table = 'category_products';
public function product()
{
return $this->belongsTo('Product');
}
public function category()
{
return $this->belongsTo('Category');
}
}
Update
New question on this
I am trying to display products. If the category is not passed (value is -1), I will show all products, otherwise I will show products from the passed category.
Now that I show all the products, those products may already exist in the category. I want to show a checked box for products that are already in a category. I am doing something like this:
if($category_id==-1)
$products = Product::where('status', '=', 'active')->take($count)->skip($skip)->get();
else{
$products = Product::whereHas('categories', function($q) use ($category_id)
{
$q->where('category_id', $category_id);
})->where('status', 'active')
->take($count)
->skip($skip)
->get();
}
The category_products table has product_id, category_id as columns.
Now the request:
$ products = Product :: where ('status', '=', 'active') → take ($ count) → skip ($ skip) → get ();
will only select products from the product table . If I check each product to see if it exists in category_products , there will be too many database queries for a large number of products.
Any idea how to achieve this. Hope I was able to clear the situation. Thanks to
source to share
The model is CategoryProduct
not needed if you have no additional fields other than product_id and category_id that point to other relationships.
Methods for setting up relationships on models Category
and are needed Product
.
In Category
add the relationship function ...
public function products()
{
return $this->belongsToMany('Product', 'category_products');
}
In your model, Product
do the same for the categories.
public function categories()
{
return $this->belongsToMany('Category', 'category_products');
}
Then you can query for your active products belonging to this category using your relationship method and whereHas()
$products = Product::whereHas('categories', function($q) use ($category_id)
{
$q->where('id', $category_id);
})->where('status', 'active')
->take($count)
->skip($skip)
->get();
source to share
You don't need a model for a pivot table in many ways. Check out this section of the Eloquent documentation for further explanation.
You still need to create a migration to set up the pivot table (or do it manually if you are not using migrations), but not the model. Instead, create a function Category
to denote the relationship:
public function products()
{
return $this->belongsToMany('App\Product', 'category_products');
// - You might need to adjust the namespace of App\Product
// - category_products refers to the pivot table name
}
Likewise, Product
a similar public function is required.
Then you can do it the other way around by finding a category and then listing all related products:
$products = Category::find($category_id)
->products()
->where('status', 'active')
->take($count)
->skip($skip)
->get();
This question may also be relevant to yours.
source to share