Laravel - How do I retrieve dates from my database, and show only date without duplicates?

D West 0
Viewed 33

I'm working on a Laravel Jetstream Project, using Inertia.js.

The purpose of the project is to show the GPS history, of cars from a tracker, and later map them out, to get the full geo-history, for a specific date. The models I have in this project includes: 'Fleet (the car(s))', 'Tracker' and 'History'.

Currently I am able to show all the dates, from which the tracker has sent it's location for a specific vehicle. However, I'm having trouble ordering the dates, so that I ONLY get the latest entry for each date, and NOT multiple entries for that single date.

Also, I only wish to show the date, without the timestamp - Like such: 2022-08-18, instead of 2022-08-18 09:21:06

Here is my HistoryController:

 class HistoryController extends Controller
        {
            public function index(Request $request)
            {
                
                $fleet = Fleet::findOrFail($request->id);
                $history = DB::table('history')->where('imei', $fleet->tracker->value('imei'))->groupBy('created_at')->get();
        
        
                return Inertia::render('FleetView', [
                    'fleet' => $fleet,
                    'history' => $history
                ]);
            }
        }

And my History Model:

    class History extends Model
    {
        use HasFactory;
    
        protected $table = 'history';
    
        protected $fillable = [
            'id', 'imei', 'lat', 'lng', 'speed', 'msg_type', 'time',  'date', 'unknown_char', 'created_at', 'payload'
        ];
    
        public function tracker()
        {
            return $this->hasOne(Tracker::class, 'imei');
        }
    }
php-carbon laravel vue.js
1 Answers

Here was the solution:

$fleet = Fleet::findOrFail($request->id);

        $history = DB::table('history')->where('imei', $fleet->tracker->value('imei'))->groupByRaw('Date(created_at)')->latest()->get();

    

        foreach($history as $hdItem){
            
            $hdItem->created_at = Carbon::parse($hdItem->created_at)->format('d.m.Y');
            
        }

        foreach($history as $htItem){
            $htItem->time = Carbon::parse($htItem->time)->format('H:i:s');
        }
 
        

        return Inertia::render('FleetView', [
            'fleet' => $fleet,
            'history' => $history
        ]);
D West0
Related