Laravel/Eloquent, retrieve data dynamically (whereIn, sum)

Question

I am pretty noob in Laravel, and I would like to retrieve data from DB in an "automated" way. I have a query that get the value of key "cases" where 'state' is the latest repeated

$pieWyoming=State::select('cases')->where('state', '=', 'Wyoming')->orderByDesc('id')->limit(1)->get()->sum('cases');

But I want to do this with whereIn

$statesArr = array("Alabama", "Alaska", "Arizona", "Arkansas", "California", "Colorado", "Connecticut", "Delaware", "District of Columbia", "Florida", "Georgia", "Guam", "Hawaii", "Idaho", "Illinois", "Indiana", "Iowa", "Kansas","Kentucky","Louisiana","Maine","Maryland","Massachusetts","Michigan","Minnesota","Mississippi","Missouri", "Montana", "Nebraska", "Nevada", "New Hampshire", "New Jersey", "New Mexico", "New York", "North Carolina", "North Dakota", "Northern Mariana Islands", "Ohio", "Oklahoma", "Oregon", "Pennsylvania", "Puerto Rico", "Rhode Island", "South Carolina", "South Dakota", "Tennessee", "Texas", "Utah", "Vermont", "Virgin Islands", "Virginia", "Washington", "West Virginia", "Wisconsin", "Wyoming");

$Wyoming=State::select('cases')->whereIn('state',$statesArr)->orderByDesc('id')->limit(1)->get()->sum('cases');

But it seems that this traverse $statesArr and only gets the last value, that is correct but is only one value from one state, I want ALL VALUES from ALL states

EDIT, SAMPLE DATA AND EXPECTED OUTPUT

The database holds data as this https://res.cloudinary.com/dcdajhifi/image/upload/v1599003808/ex1_ys5ksi.png

and I would like to get only the value of field "cases" for each state in array $statesArr, each state should be the last repeated, in example https://res.cloudinary.com/dcdajhifi/image/upload/v1599003808/ex2_lby8md.png

here is the last time ALABAMA appears, so I would like to get the value of cases for this row, THIS for each state. So I could create a pannel where I can display the state name with it's value cases and deaths, without doing a query for each state.

Answer 1

To pick the latest entry (based on latest autoincrement id) for each state you can do a self join with your table like

DB::table('states as a')
  ->select('a.*')
  ->leftJoin('states as b', function ($join) {
        $join->on('a.state', '=', 'b.state')
             ->whereRaw(DB::raw('a.id < b.id'))
             ;
   })
  ->whereNull('b.id')
  ->whereIn('a.state',$statesArr)
  ->get();

If you are using latest version of laravel you can rewrite above using inner join

$latestStates = DB::table('states')
                   ->select(DB::raw('max(id) as max_id'))
                   ->groupBy('state');

$states = DB::table('states as a')
        ->joinSub($latestStates, 'b', function ($join) {
            $join->on('a.id', '=', 'b.max_id');
        })->whereIn('a.state',$statesArr)
          ->get();

Or you can use whereExists

$states = DB::table('states as a')
           ->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('states as b')
                     ->whereRaw('a.state= b.state')
                     ->groupBy('b.state')
                     ->havingRaw('max(b.id) = a.id')
                     ;
           })->whereIn('a.state',$statesArr)
             ->get();