How to get data HasMany() using WhereHas in Laravel

Question

I want to get data from my table " Package " by using its model " Package " and in this model " Package " it have a HasMany() named histories() relation to model " History "

so i want to only get data that have histories

here is my controller

public function getIncomeMPW(Request $request)
{
    if ($request->expectsJson()) {
        $this->getSearch($request);
        $query = new Package();
        $query->with(['histories', 'items', 'items.prices', 'origin_regency', 'origin_district', 'origin_sub_district', 'destination_regency', 'destination_district', 'destination_sub_district', 'code', 'attachments']);

        $query->whereHas('histories', function (Builder $query) {
            $query->whereNotNull('partner_id');
        });

        $query->orderBy('created_at', 'desc');

        return (new Response(Response::RC_SUCCESS, $this->query->paginate(request('per_page', 15))))->json();
    }

}

here is my Package model relation histories HasMany()

public function histories(): HasMany
{
    return $this->hasMany(History::class, 'package_id', 'id');
}

and last here is my response that showing right now

i already try using whereHas(), Has(), whereDoesntHave(), and its seems like there is no impact on my response, can anyone help me please ?

Are you sure `$query = new Package();` is actually creating a query? I've never seen this syntax. Can you try `$query = Package::with(['histories' ...`? — Frnak, Sep 30 '21 at 09:34
fyi, i already can show the data like in the screenshot, but the only thing i want is to filter the data that doesnt have histories to not showing in my response — M Andre Juliansyah, Sep 30 '21 at 09:37

Frnak · Accepted Answer · 2021-09-30T09:58:16.253

In your response you simply access a different query as it seems.

return (new Response(Response::RC_SUCCESS, $this->query->paginate(request('per_page', 15))))->json();

Uses $this->query

While

$query = new Package();
$query->with(['histories', 'items', 'items.prices', 'origin_regency', 'origin_district', 'origin_sub_district', 'destination_regency', 'destination_district', 'destination_sub_district', 'code', 'attachments']);

$query->whereHas('histories', function (Builder $query) {
  $query->whereNotNull('partner_id');
});

$query->orderBy('created_at', 'desc');

Defines a $query without $this. I'd expect your $this->getSearch($request); to define $this->query (as the function is not posted in the question, i cannot tell). So either remove $this in your response - or change everything to $this and ensure to now overwrite it in the first line.

Quickfix should be

return (new Response(Response::RC_SUCCESS, $query->paginate(request('per_page', 15))))->json();

UPDATE:

Quick answer: Change

return (new Response(Response::RC_SUCCESS, $this->query->paginate(request('per_page', 15))))->json();

To

return (new Response(Response::RC_SUCCESS, $query->paginate(request('per_page', 15))))->json();

thank you for your attention to my question, but it seems like that doesnt fix my whereHas problem, i already put wherehas in my controller code but the response keep showing the data that have empty histories [] — M Andre Juliansyah, Sep 30 '21 at 09:51
Well then you obviously did not post enough of your code. You clearly don't reference the same query in your response as you do when setting it up. Hence, the `whereHas` you share in your code has no effect at all. What should `i already put wherehas in my controller code`even mean? You only shared your controller?! It doesn't seem you fully read my answer. — Frnak, Sep 30 '21 at 09:55

score 0 · Answer 2 · answered Sep 30 '21 at 11:07

Wwhat whereHas and whereDoesntHave functions do in the backstage is that they make a sub query such as:

Select * from packages where exists (select * from histories where CONDITIONS YOU HAVE MENTIONED)

And the problem here is that when you use with method you eager load table history which adds one extra query that is not related to the first one such as:

Select * from histories where package_id in (1,2,3,4,5,6)

So since we cleared that out, what I suggest you do is that you assign a function to a variable in this way:

$historyFunction = function ($query) {
            $query->whereNotNull('partner_id');
        };

and than call it in with and in whereHas methods as shown below:

 $query->with(['histories' => $historyFunction, otherRelations... ]);
 $query->whereHas('histories', $historyFunction);

And what this does is that it tells eloquent: When you eager load Histories relationship add this conditions to the query you are about to make.

How to get data HasMany() using WhereHas in Laravel

2 Answers2