Get model by min() one of fields grouped by another field

I would consider using as is with db facade, for an example see:

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Give me minute, for example of eloquent query:

Not your data, just an eloquent query example:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Just use a little trial and error to work it out.

Thank you, but I see that you join another table while I need to join a result of subquery on the same table. Sorry, but I can't imagine how I can apply your approach to my problem.

Can you give a deeper explanation?

I tried to do this:

$s1 = \DB::table('schedules')
    ->selectRaw('event_id, min(datetime) as min_date')
    ->groupBy('event_id');

$s = \DB::table('schedules as s')
    ->joinSub($s1, 's1', function (Builder $join) {
        $join->on('s.event_id', '=', 's1.event_id');
    })
    ->where('s.datetime', '=', 's1.min_date')
    ->get('s.*');

But got the error:

Illuminate\Database\QueryException : SQLSTATE[22007]: Invalid datetime format: 7 ERROR: invalid input syntax for type timestamp: "s1.min_date"

Did you try this in the DB facade.

Edit I usually use getPdo ()

https://laracasts.com/discuss/channels/guides/getpdo-usage see last example.

Dates are in that example.

I solved this problem simpler:

$schedules = Schedule::selectRaw('DISTINCT ON (event_id) *')
    ->orderBy('event_id')
    ->orderBy('date')
    ->get();

But, as far as I know, this query will work only with PostgreSQL.

The bestes answer for this even though its only work with PostgreSQL

Thank you for giving you valuable time https://www.myprepaidcenter.page/