Select clients where latest appointment is older than 30 days

$clients = Client::whereHas('appointments', function($q) {
    $q->where('start_date', '<', Carbon::now()->subDays(30)->format('Y-m-d'))->latest()->groupBy('client_id');
})->get();

This does not work @SaeedPrez. latest() orders my appointments by created_at column which has not to be the latest appointment. I came up with this but this also does not work:

$clients->whereHas('appointments', function($q) use ($val) {
  $q->whereRaw("date(start_date) < '" . Carbon::now()->subMonths($val)->format('Y-m-d') . "'")
    ->orderBy('start_date','desc')
    ->limit(1)
    ->groupBy('client_id');
});

select * from `clients`
where `clients`.`deleted_at` is null
  and (
    select count(*)
    from `appointments`
    where `appointments`.`client_id` = `clients`.`id` and date(start_date) < '2016-01-15' and `appointments`.`deleted_at` is null
    group by `client_id`
    order by `start_date` desc limit 1
  ) >= 1
limit 25

$clients = Client::whereHas('appointments', function($q) {
    $q->where('start_date', '<', Carbon::now()->subDays(30)->format('Y-m-d'))
    ->orderBy('start_date', 'desc')
    ->groupBy('client_id');
})->get();

Don't add ->limit(1)

No :( Just get clients with appointments which are not older than 30 days. Yes, limit(1) is not necessary because groupBy produces just one row.

Aha, I thought you wanted to get only latest appointment..

But I still don't understand... first you said..

I want to select all clients where the LAST related appointment (start_date) is older than 30 days.

Now you say ..

Just get clients with appointments which are not older than 30 days.

Older or not older? I'm very confused..

$clients = Client::whereHas('appointments', function($q) {
    $q->where('MAX(start_date)', '<', Carbon::now()->subDays(30)->format('Y-m-d'))
    ->orderBy('start_date', 'desc')
    ->groupBy('client_id');
})->get();

I want to get all clients where the latest related appoint is older than x days.

My current query looks like this:

select * from `clients`
where `clients`.`deleted_at` is null
and (
  select count(*) from `appointments` where `appointments`.`client_id` = `clients`.`id` and date(start_date) < '2016-01-15' and `appointments`.`deleted_at` is null
  group by `client_id`
  order by `start_date` desc
) >= 1
limit 25

But i get clients with latest appointment from start_date = 2016-04-13 11:00

Did you try the last code I gave you with MAX(start_date) ?

You still confuse the living hell out of me...

I want to get all clients where the latest related appoint is older than x days.

But i get clients with latest appointment from start_date = 2016-04-13 11:00

2016-04-13 is older than x days, that is what you said you wanted :/

Sorry in my query i used date(start_date) < '2016-01-15'

SQLSTATE[HY000]: General error: 1111 Invalid use of group function (SQL: select count(*) as aggregate from `clients` where `clients`.`deleted_at` is null and (select count(*) from `appointments` where `appointments`.`client_id` = `clients`.`id` and MAX(start_date) < '2016-01-15' and `appointments`.`deleted_at` is null group by `client_id` order by `start_date` desc) >= 1)

i also get this error when i'm not using the groupBy(). Hmm

Any particular reason why you do not just orderBy() your appointments of a user by whatever date is applicable and then compary ->first() with your condition?

How you would do this in eloquent @EventFellows ? :)

You could also make another relation 'latestAppointment' on your client model and then

$clients = Client::whereHas('latestAppointment', function ($query) {
    $query->'start_date', '<', Carbon::now());
})->get();

This works for me:

$clients->whereHas('appointments', function($q) use ($val) {
  $q->havingRaw("MAX(start_date) < '".Carbon::now()->subMonths($val)->format('Y-m-d')."'");
});

Late to the party but is there any reason you aren't using a query scope with one of the Additional Where Clauses in the Query Builder section of the docs:

whereDate / whereMonth / whereDay / whereYear