Advanced where Laravel eloquent query

Question

I'm currently stuck on a single where Laravel query. The problem is, I want to filter some data depending on latest record from a given table. Currently, my query looks like this:

$clients = $clients->whereDoesntHave('bill')->orWhereHas('bill', function ($query) use ($activeSchoolYear){
     $query
         ->where('created_at', '<', Carbon::parse($activeSchoolYear->start));
 })

Where $activeSchoolYear->start is today's date.

The problem occurs if there are multiple records in my bills table. So for an example, if I have two records with separate dates:

ID | Name  | created_at          |
1  | test  | 2019-09-09 15:31:25|
2  | test2 | 2018-09-10 16:42:44|

The query still return both dates, even if one of them is false. I think that query looks for all the records in database, and still returns the given user, because one of the dates is correct. My question is, is it possible to target only the latest row from database. Or this can't be done using eloquent. Any help is appreciated.

EDIT:

As @Caddy suggested, I used a query scope, but I modified it a bit:

public function scopeNoActiveBill($query){
  return $query
            ->groupBy('created_at')->take(1)
            ->where('created_at', '<=', Carbon::create(2019, 9, 1));
}

I tried to take only the last row from the bills table. Then, inside controller, this is my query:

$clients = $clients->whereDoesntHave('bill')
     ->orWhereHas('bill', function ($query) use ($activeSchoolYear){
            $query->noActiveBill();
      })
      ->whereHas('contracts', function ($query) use ($request) {
            $query
                  ->where('contract_type', '<', 2)
                  ->where('signed', 1)
                  ->where('school_year_id', $request['school_year']);
       });

So, I want both clients without any bills, or clients with bills that fit the given criteria. The query works fine if client has only one bill, but the problem is when client has more than one bill. If any of those bills fits the given scope, it returns the bill. I want to only check the latest record, and return the bill if that records fits the scope criteria.

EDIT 2:

Thanks to @Caddy DZ's asnwer, I managed to get desired result. I just changed condition to watch for clients that don't have bills created past given date. Query scope:

public function scopeNoActiveBill($query){
     return $query->where('created_at', '>=', Carbon::create(2019, 9, 1));
}

Controller:

$clients = $clients->whereDoesntHave('bill')
    ->orWhereDoesntHave('bill', function ($query) use ($activeSchoolYear){
        $query->noActiveBill();
    });

Answer 1

What you're looking for is relationship query scopes like this

$clients = App\Client::with(['bills' => function ($query) {
   $query->today();
}])->get();

Add today() scope to your Bill model like so
Bill.php

public function scopeToday($query)
{
    // Change today by $activeSchoolYear if necessary
    return $query->whereDate('created_at', '<', today());
}

Assuming a hasMany relationship in the Client

public function bills()
{
    return $this->hasMany(Bill::class);
}

And data seeded like this
DatabaseSeeder.php

// Will be returned because whereDoesntHave('bills')
$clientWithoutBills = Client::create([]);
$client = Client::create([]);
$client->bills()->createMany([
    [
        'name' => 'test',
        'created_at' => Carbon\Carbon::yesterday()
    ],
    [
        // Will not be returned because created today (not < today)
        'name' => 'test1'
    ],
    [
        // Will not be returned because created tomorrow
        'name' => 'test2',
        'created_at' => Carbon\Carbon::tomorrow()
    ]
]);

Results

[
    {
        "id": 1,
        "created_at": "2019-09-09 16:20:24",
        "updated_at": "2019-09-09 16:20:24",
        "bills": []
    },
    {
        "id": 2,
        "created_at": "2019-09-09 16:20:24",
        "updated_at": "2019-09-09 16:20:24",
        "bills": [
            {
                "id": 1,
                "name": "test",
                "client_id": 2,
                "created_at": "2019-09-08 00:00:00",
                "updated_at": "2019-09-09 16:20:24"
            }
        ]
    }
]

Hope this helps

Answer 2

You can use Eloquent whereDate().

->whereDate('created_at', '<', Carbon::parse($activeSchoolYear->start));