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.

That is not the behavior of [whereIn()](https://laravel.com/docs/7.x/queries#where-clauses). whereIn() will match any value in the array provided. — Kurt Friars, Sep 01 '20 at 19:01
Are you wanting to get the latest row from each state in the array? — Rwd, Sep 01 '20 at 19:22
@Rwd Exactly, it is a CSV where states names repeat a lot but I want to get the last time it is repeated and from that row get the value of cases, this for each state — joecort, Sep 01 '20 at 19:52
@joecort can you include sample data of states and also add the expected output for each state from the sample data set — M Khalid Junaid, Sep 01 '20 at 20:23

maki10 · Answer 1 · 2020-09-02T09:11:32.683

Try like this:

$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::whereIn('state', $statesArr)->orderByDesc('id')->sum('cases');

it throws this: Call to a member function whereIn() on float — joecort, Sep 02 '20 at 05:26
@joecort Answer updated. I forgot that `sum` is like `get` so it hit DB on call. — maki10, Sep 02 '20 at 09:12

M Khalid Junaid · Accepted Answer · 2020-09-02T06:00:33.863

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();

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

2 Answers2