Consider the following three tables:
- items (id, name, description, subcategory_id)
- categories (id, name)
- subcategories (id, name, category_id)
I am trying to implement a search functionality for my first Laravel e-commerce project. I am asking the user to provide two things to run a search: a) The query string b) The category (not subcategory)
If I had the category_id in my items table, I could have gotten away with a simple query like this:
SELECT * FROM `items` WHERE name LIKE 'A%' AND category_id = '1';
But, since the category_id is in the subcategories table I have to run a nested statement like this:
SELECT * FROM `items` WHERE name LIKE 'A%' AND subcategory_id IN (SELECT id FROM sub_categories WHERE category_id = '1')
I have the dedicated Models for each of the tables (i.e. Item, Category, Subcategory) and the relationship between the Category and Subcategory is established via the hasMany() and belongsTo() methods.
Category.php:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Category extends Model
{
protected $fillable = [
'name',
];
public function subcategories(){
return $this->hasMany(SubCategory::class);
}
}
SubCategory.php:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class SubCategory extends Model
{
protected $fillable = [
'name', 'category_id',
];
public function category(){
return $this->belongsTo(Category::class);
}
}
How do I transform the above-mentioned traditional SQL query into the equivalent Eloquent query?