CODEWITHSUNDEEP
laravellaravel-5eloquent
Matthew
Matthew

Reputation: 1655

Return collection where attribute of model records is empty in Laravel

I have the following section of one of my functions:

$shipmentsNotSent = DB::table('shipments')
        ->whereNull(['billedAt','agentBilling'])
        ->whereBetween('date', [$startDateNS, $nowNS])
        ->get();

This works well but I am currently in the process of streamlining the models/controllers and a variety of other files, and I need it also to return only records that return nothing in the custom attribute (see below) I set up for the Shipment model.

public function getbillingUploadStatusAttribute(){
    $billingUploadStatus = $this->billingUpdates()->where([
        ['actionCategoryID', 1],
        ['actionID', 2]
    ])->orderBy('created_at','desc')->first();
    return $billingUploadStatus;
}

So how would I go about filtering those records already filtered in the above controller function also return nothing in the attribute return?

Upvotes: 0

Views: 77

Answers (2)

Tim Lewis
Tim Lewis

Reputation: 29258

I would think you're looking for ->whereHas():

->whereHas("billingUpdates", function($subQuery){
    $subQuery->where("actionCategoryID", "=", 1)->where("actionID", "=", 2);
});

That subQuery would essentialy performa the same ->where()s that you're using in the function, but when used in a whereHas context, will constrain the parent Model records to those that match.

Note: This can't be used with DB::table("shipments"), but would rather need to be used on the associated Model (Shipment, I'm assuming). The full query would look like:

$shipmentsNotSent = Shipment::whereHas("billingUpdates", function($subQuery){
    $subQuery->where("actionCategoryID", "=", 1)->where("actionID", "=", 2);
})->whereNull("billedAt")
->whereNull("agentBilling"])
->whereBetween('date', [$startDateNS, $nowNS])
->get();

Edit: If you're looking to query for the non-existence of this function, use ->whereDoesntHave():

$shipmentsNotSent = Shipment::whereDoesntHave("billingUpdates", function($subQuery){
    $subQuery->where("actionCategoryID", "=", 1)->where("actionID", "=", 2);
})->whereNull("billedAt")
->whereNull("agentBilling"])
->whereBetween('date', [$startDateNS, $nowNS])
->get();

Upvotes: 2

abr
abr

Reputation: 2129

I believe you're looking for this, which is the opposite to whereHas as @Tim Lewis mentioned. Lewis gives a fine explanation on how it works, so theres no need to copypasta his legit answer content

https://laravel.com/api/5.6/Illuminate/Database/Eloquent/Concerns/QueriesRelationships.html#method_whereDoesntHave

The query would look like:

//Assuming Shipments is your Eloquent Model
Shipments::whereDoesntHave("billingUpdates", function($advancedWhereDoesntHave){
    $advancedWhereDoesntHave->where("actionCategoryID", "=", 1)->where("actionID", "=", 2);
})->whereNull(['billedAt','agentBilling'])
  ->whereBetween('date', [$startDateNS, $nowNS])
  ->get();

Upvotes: 0

Related Questions