How to get data HasMany() using WhereHas in Laravel-CodePudding

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 ?

CodePudding user response：

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();

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

CodePudding user response：

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.