Laravel display relation results

Question

From the URL I am getting either a $filter, $month or both. When I for example want to show only results for November, everything works fine using the code below. But, when a filter has been set ALSO, then it shows all results (also from other months), if the filter has been found in the database.

What I want to achieve is, show only the results if the filter that was set has been found in the database, AND if a month has been set, only show the results from that particular month also.

My Section and Expense models have a belongsTo and hasMany relation respectively.

$query = Section::query();

If a filter has been set:

    if ($filter !== 'false') {
        $query->with(['expenses' => function ($query) use ($month) {
            $query->where('date', 'LIKE', '%2015-' . Carbon::parse($month)->format('m') . '%');
        }]);
    }

If a month has been set:

    if ($month !== 'false') {
        $query->with(['expenses' => function ($query) use ($filter) {
            $query->where('type', '=', $filter);
        }]);
    }

I also tried the following code:

    if ($filter !== 'false') {
        $query->with('expenses')->whereHas('expenses', function($query) use ($filter) {
            $query->where('type', '=', $filter);
        });
    }

    if ($month !== 'false') {
        $query->with('expenses')->whereHas('expenses', function($query) use ($month) {
            $query->where('date', 'LIKE', '%2015-' . Carbon::parse($month)->format('m') . '%');
        });
    }

$expenses = $query->orderBy('section', 'asc')->paginate(25);

I use a foreach on the $expenses in my view to display the results.

score 1 · Accepted Answer · answered Dec 03 '15 at 05:07

With the way you have it, you're overwriting one eager loading constraint with another. You need to move all your checks into one eager loading constraint, like so:

$query->with(['expenses' => function ($q) use ($month, $filter) {
    if ($month !== 'false') {
        $q->where('date', 'LIKE', '%2015-' . Carbon::parse($month)->format('m') . '%');
    }
    if ($filter !== 'false') {
        $q->where('type', '=', $filter);
    }
}]);

Awesome, and so simple I am ashamed :) – Hardist Dec 03 '15 at 07:57 — Hardist, Dec 03 '15 at 07:57

score 1 · Answer 2 · answered Dec 03 '15 at 05:18

You need to combine both, else the last one is messing with the state set by the first eloquent model.

For a quick fix and understand whats happening try:

   if ($filter !== 'false') {
        $query = $query->with('expenses')->whereHas('expenses', function($query) use ($filter) {
            $query->where('type', '=', $filter);
        });
    }

    if ($month !== 'false') {
        $query = $query->with('expenses')->whereHas('expenses', function($query) use ($month) {
            $query->where('date', 'LIKE', '%2015-' . Carbon::parse($month)->format('m') . '%');
        });
    }

$expenses = $query->orderBy('section', 'asc')->paginate(25);

In the correct scenenario you should handle all AND conditions inside the inner closure.

it can be done using

$query->where(condition1);
$query->where(condition2);

or

$query->where(condition1)
      ->where(condition2);

Take a look to this answer and this documentation

Laravel display relation results

2 Answers2