Skip to content
Advertisement

Laravel with() and search using LIKE not working

So I have 2 tables, 1 table for storing debt(id, amount, category_id) and 1 table for storing debt categories(id, name). I am trying to pull the data for each month from the debt table, but I also have a search which seems to not work, I guess I am missing something.

I have the following:

    $debt = $this->debtModel
        ->select(DB::raw('MONTH(created_at) as month'), DB::raw('SUM(amount) as amount'), 'category_id')
        ->where('user_id', Auth::user()->id)
        ->whereYear('created_at', $year)
        ->with(['category' => function ($query) use ($filter) {
            $query->where('name', 'like', "%$filter%");
        }])
        ->orderBy('month', 'asc')
        ->groupBy('month')
        ->groupBy('category_id')
        ->get();

Debt Model:

public function category()
{
    return $this->hasOne('AppModelsDebtCategory', 'id', 'category_id');
}

This works fine, with the exception of search, If I try to filter by a category name it still returns everything.

Advertisement

Answer

try with

->whereHas('category' , function ($query) use ($filter) {
            $query->where('name', 'like', "%$filter%");
        })

instead of

->with(['category' => function ($query) use ($filter) {
            $query->where('name', 'like', "%$filter%");
        }])

with() will just loads the relationship not filtering result.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement