Select() with DB:raw()

what is $this? Normally it should be a Object (Model or DB) in order to apply select method to it.

You could do:

return DB::table('champion')
          ->select(
            DB::raw("DATEDIFF(IFNULL(DATE(champions.lost_on), NOW()), DATE(champions.won_on)) as length, player_id")
          )
          ->orderBy('length', 'desc')
          ->groupBy('length', 'player_id')
          ->limit(1)
          ->get();

I'm calling the method like this. Is it okay to call the model instead with the following even though this is a custom collection class?

return Champion::class()->select()....

@foreach ($title->champions->longest_title_reign() as $reign)

You can include the Champion Model at the top of the file with use App\Champion; and then just Champion::select()... or just App\Champion::select().... Not certain about Champion::class()->select()... but it looks like it should work.

So this should work out:

return App\Champion::select(
    DB::raw("DATEDIFF(IFNULL(DATE(champions.lost_on), NOW()), DATE(champions.won_on)) as length, player_id")
  )
->orderBy('length', 'desc')
->groupBy('length', 'player_id')
->limit(1)
->get();

You can even do this I think since you want only the first result:

return App\Champion::select(
    DB::raw("DATEDIFF(IFNULL(DATE(champions.lost_on), NOW()), DATE(champions.won_on)) as length, player_id")
  )
->orderBy('length', 'desc')
->groupBy('length', 'player_id')
->first();

As for the foreach loop, what are you trying to do there? It seems you have a $title collection. I do not see the relation to the first query.

@xmarks What's odd though is the player_id isn't the correct player that has held that title the longest and in some situations has never even held the title. So I need it to know what title needs to be specified. I thought it would know that with going through the $title->champions relationship though.

Anyone have ideas on this?

I've had other try and assist but no solutions have been developed yet. Anyone with an idea?

I'm not exactly certain I understand your request here. I would probably need to check your Database connections and require more information of your logic. There are many unknowns here for me.

• What is Title and what fields does it have
• What fields does Champion have
• What does longest_title_reign() do

As for the first query, I was wondering if you need to ->groupBy('length', 'player_id')? Otherwise, this seems like it should return the correct player_id.

Also, you might consider a "Hack" where you can update a days_reigned field on champions manually each time won_on is updated, so you do not need to make a comparison for each row at this point.

You will only have to sort champions by days_reigned and get the first result. No need for DB::raw either