Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

tanmay_das's avatar

Equivalent Eloquent query

Consider the following three tables:

  1. items (id, name, description, subcategory_id)
  2. categories (id, name)
  3. 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?

0 likes
4 replies
mdecooman's avatar

Hi @tanmay_das

You want to add a hasMany relation to your Subcategory class to link with items, then something like that should do it (not the only way to do it though).

$category  = new Category;
$category->where('id', '=', 1)->with('subcategories')->with('items')->where('name','=','whatever')->get()

Maybe your schema would be better with pivot tables...

bwrice's avatar

Add the hasManyThrough relation to Category:

public function items()
{
 return $this->hasManyThrough('Item::class', 'SubCategory::class');
}

And then you can do this:

$items = Category::find(1)->items()->where('name', 'like', '%A')->get();
1 like
tanmay_das's avatar

Thanks guys, but you know what... now I have added two more tables: location and sublocation.

@bwrice If I follow your suggestion and add a hasManyThrough() relation also in the Location model, how would I then call the Category::find() and Location::find methods at a time to get all the $items?

Please or to participate in this conversation.