Laravel use WHERE with relation

Question

I need to filter categories by name. Category name is stored in separated table category_details. When i listing this in blade all is good but when i need to filter it its show

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'details.name' in 'where clause'

Controller method

   public function index(Request $request)
    {
        $categories = Category::with('details')
            ->when($request->has('name'), function ($q) use ($request) {
                return $q->where('details.name', 'like', $request->query('name') .'%');
            })
            ->paginate(15);
        
       

           return view('ecommerce::admin.catalog.category.index', compact('categories'));
    }

Here is model method

 public function details()
 {
     return $this->hasOne(CategoryDetails::class, 'category_id', 'category_id');
 }

How can i achive this?

The column is definitely `name` yeah, and not something like `category_name`? — Andy Holmes, Jul 04 '23 at 21:55
Does this answer your question? [Laravel Eloquent Filter By Column of Relationship](https://stackoverflow.com/questions/20801859/laravel-eloquent-filter-by-column-of-relationship) — miken32, Jul 06 '23 at 16:57

score 1 · Answer 1 · answered Jul 04 '23 at 22:05

Fixed

public function index(Request $request)
    {
        $categories = Category::with('details')
            ->when($request->has('name'), function ($q) use ($request) {
                return $q->whereHas('details', function ($query) use ($request) {
                    return $query->where('name', 'like', '%'. $request->query('name') .'%');
                });
            })
            ->paginate(15);

        $filter_categories = Category::with(['details', 'childrenRecursive'])->where('parent_id', '=', null)->get();

        return view('ecommerce::admin.catalog.category.index', compact('categories', 'filter_categories'));
    }

To explain a little, `with` doesn't do what a lot of people expect; it doesn't add a `JOIN` to the query. Instead, it runs another query *after* the main query, to fetch all the matching rows in the relationship. Thus, `details.name` isn't available. — ceejayoz, Jul 04 '23 at 22:09

score 1 · Answer 2 · answered Jul 05 '23 at 06:52

1

Laravel has a function for this. You can simply use whereRelation. (Laravel docs)

    public function index(Request $request)
{
    $categories = Category::with('details')
        ->whereRelation('details', 'name', 'like', $request->query('name') . '%')
        ->paginate(15);

    return view('ecommerce::admin.catalog.category.index', compact('categories'));
}

answered Jul 05 '23 at 06:52

luukd

356
8

This is more simplest than my solution – Ivan Jul 06 '23 at 21:30

Laravel use WHERE with relation

2 Answers2