hunterhawley

hunterhawley

Member Since 3 Months Ago

Experience Points
2,080
Total
Experience

2,920 experience to go until the next level!

In case you were wondering, you earn Laracasts experience when you:

  • Complete a lesson — 100pts
  • Create a forum thread — 50pts
  • Reply to a thread — 10pts
  • Leave a reply that is liked — 50pts
  • Receive a "Best Reply" award — 500pts
Lessons Completed
8
Lessons
Completed
Best Reply Awards
0
Best Reply
Awards
  • start-engines Created with Sketch.

    Start Your Engines

    Earned once you have completed your first Laracasts lesson.

  • first-thousand Created with Sketch.

    First Thousand

    Earned once you have earned your first 1000 experience points.

  • 1-year Created with Sketch.

    One Year Member

    Earned when you have been with Laracasts for 1 year.

  • 2-years Created with Sketch.

    Two Year Member

    Earned when you have been with Laracasts for 2 years.

  • 3-years Created with Sketch.

    Three Year Member

    Earned when you have been with Laracasts for 3 years.

  • 4-years Created with Sketch.

    Four Year Member

    Earned when you have been with Laracasts for 4 years.

  • 5-years Created with Sketch.

    Five Year Member

    Earned when you have been with Laracasts for 5 years.

  • school-session Created with Sketch.

    School In Session

    Earned when at least one Laracasts series has been fully completed.

  • welcome-newcomer Created with Sketch.

    Welcome To The Community

    Earned after your first post on the Laracasts forum.

  • full-time-student Created with Sketch.

    Full Time Learner

    Earned once 100 Laracasts lessons have been completed.

  • pay-it-forward Created with Sketch.

    Pay It Forward

    Earned once you receive your first "Best Reply" award on the Laracasts forum.

  • subscriber-token Created with Sketch.

    Subscriber

    Earned if you are a paying Laracasts subscriber.

  • lifer-token Created with Sketch.

    Lifer

    Earned if you have a lifetime subscription to Laracasts.

  • lara-evanghelist Created with Sketch.

    Laracasts Evangelist

    Earned if you share a link to Laracasts on social media. Please email [email protected] with your username and post URL to be awarded this badge.

  • chatty-cathy Created with Sketch.

    Chatty Cathy

    Earned once you have achieved 500 forum replies.

  • lara-veteran Created with Sketch.

    Laracasts Veteran

    Earned once your experience points passes 100,000.

  • 10k-strong Created with Sketch.

    Ten Thousand Strong

    Earned once your experience points hits 10,000.

  • lara-master Created with Sketch.

    Laracasts Master

    Earned once 1000 Laracasts lessons have been completed.

  • laracasts-tutor Created with Sketch.

    Laracasts Tutor

    Earned once your "Best Reply" award count is 100 or more.

  • laracasts-sensei Created with Sketch.

    Laracasts Sensei

    Earned once your experience points passes 1 million.

  • top-50 Created with Sketch.

    Top 50

    Earned once your experience points ranks in the top 50 of all Laracasts users.

Level 1
2,080 XP
21 Sep
2 days ago

hunterhawley left a reply on Best Storage Method

I'll look into this. Thanks! This is my first app I'm developing and Laracasts always comes through to help!

hunterhawley started a new conversation Best Storage Method

Hey y'all,

Quick question here: I'm working on the proposed solution to the following question (https://laracasts.com/discuss/channels/laravel/i-dropped-my-query-count-by-29980-but-page-still-takes-30-seconds-to-load) but could use a little guidance.

In this case, I am trying to cache a bunch of calculated basketball stats (Assists, Shooting percentage, etc.). For simplicity sake, assume we are just caching season-wide stats (I'll do other calculations but they will follow this same procedure).

Which storage type should I use for this? I basically need a table like this (https://ibb.co/M7cZ2vt) to be re-creatable from whatever I do. I don't really want to do JSON (personal preference) but if there is a case for why that is best, I'm up for it.

Should I maybe store as an array? Let me know what you think.

19 Sep
4 days ago

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

@snapey I do only need the stats to change when new one's are posted. I have a perfect spot to hook the update job in. I've got some work to do this weekend it seems haha

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

@snapey would model caching be easier? Or would it work as well?

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

I was afraid someone was going to tell me to tell me this haha. That sounds very hard, but Ill make it happen

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

@snapey Ok, so here is some context that is important now that I'm seeing the hydrated models. I have a Stats table, and a couple thousand+ records that HAVE to be loaded and aggregated in different ways to show up in the view (they're basketball stats and I have to add them up, like how many assists a player had in a season).

Is there anything I can do about that? As I said, all the stat records for a whole teams season have to be loaded and aggregated

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

@dalma They are. And in some cases I am filtering with where, and in every case they do have indexes on the fields.

@bugzbrown Sorry, noob question, how do I print out its output? I turned it back on and there are no unseen errors. I'll try running them in PHPMyAdmin

@antlusher 38 seconds is the time it takes the page to load

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

I don't because it is behind a login. I can get you one but until I seed a bunch of data (like my live user has) the problem won't come up for you

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

@snapey watching him now. I didn't know you could see hydrated models until now. @antlusher I am querying only from Controller (that is how I got down from 30,000)

hunterhawley left a reply on Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

Also, a couple things to note (in case this matters):

-My Google instance is: n2-standard-2 (2 vCPUs, 8 GB memory)

-I am using an Intel Cascade Lake

-I do have DebugBar disabled (that isn't what is blowing up my cpu)

-My SQL server (also on Google Cloud) stats are as follows

vCPUs: 1 Memory: 3.75 GB SSD storage: 10 GB

There was no significant CPU spike in my SQL server like there was in the compute instance when I loaded the page

hunterhawley started a new conversation Dropped Queries By ~29,980 But Still 30+ Seconds To Load...

Hey everyone!

So, the community here on Laracasts has done a lot to help me on this one. Originally (as described in my question from last week: https://laracasts.com/discuss/channels/eloquent/query-efficiency) I had a problem with the way I had my queries set up. The front page of my app was pulling around 30,000 queries and 100+MB. It was ridiculous, but since this is my first app, I'm actually just super proud that I've got those numbers down to 16 queries and 8.5MB. However, it still takes my front page 38+ seconds to load (according to DebugBar).

Now, I'm really lost here, and I'm kind of hoping somebody knows a magical artisan command that will clear a cache I don't know about and poof, my time will be down to under 2 seconds. However, I'm not sure that will be the case.

So I guess what I am asking is... What should I be asking? As stated above, DebugBar is giving me no reason to believe that this should be taking so long, but I don't even know where to look at this point. I've worked for a week to cut the queries and memory usage down, but this thing still rockets my Google cloud instance to 100% usage.

15 Sep
1 week ago

hunterhawley left a reply on Query Efficiency

Yeah, I realized that and that is what motivated me to make this post and spend my weekend fixing this haha. Over the course of a basketball season I was staring at about 30,000 queries every time someone refreshed the page (so who knows what that was going to/is costing me)

hunterhawley left a reply on Query Efficiency

I have started implementing some of your suggestions and I appreciate the thorough answer so much. I've been working all weekend but I am starting to get my query numbers down further and further. Cut my MB's/pageload from 77 to 17 and I'm not done. Thank you

14 Sep
1 week ago

hunterhawley left a reply on Query Efficiency

@tray2 Ok so here is the index function for HomeController:

public function index() {
    if(Auth::check()) {
      $userType = Auth::user()->user_type;
      $user = auth()->user();
      $users = User::all();
      $player = auth()->user();
      $players = $users->where('team_id', Auth::user()->team_id)->where('user_type', 'player');
      $genders = Gender::all();
      $sports = Sport::all();
      $leagues = League::all();
      $statisticians = User::where('user_type', 'statistician')->get();
      $team = $user->team;
      $teams = Team::all();
      $seasons = Season::all();
      $season_accesses = Season_Access::all();
      $packageAccesses = PackageAccess::all();
      $invitations = Invitation::where('team_id', $user->team_id)->get();
      $games = Game::where('team_id', $user->team_id)->orderBy('created_at', 'desc')->get();
      $issues = Issue::all();

      $statisticianGames = Game::query()
        ->fromSub(
            Game::query()
                ->select('*')
                ->selectSub(
                    Game_Assignment::query()
                        ->whereColumn('game_assignments.game_id', 'games.id')
                        ->latest()
                        ->select('game_assignments.statistician_id')
                        ->take(1),
                    'latest_user_id'
                ),
            'games'
        )
        ->where('latest_user_id', $user->id)
        ->get();

      $adminGames = Game::all();
      $unassignedGames = Game::whereDoesntHave('latestAssignment')->where(function($query)
      {
        $query->where('stats_done', '!=', 'yes')
        ->orWhere('stats_done', null);
      })->get();


      $stats = Stat::all();
      $stat_metas = Stat_Meta::all();

      if($user->using_default_pw == 'yes') {
          return view('account.reset-password', compact('user'));
      }
      else if ($userType == 'coach' && $user->team == null) {
          return view('coach.finishRegister.index', compact('genders', 'sports', 'user', 'leagues', 'seasons', 'packageAccesses', 'team'));
      }
      else {
          if(View::exists($userType . '.home')) {
            return view($userType . '.home', compact('user', 'player', 'invitations', 'users', 'players', 'games', 'statisticianGames', 'adminGames', 'seasons', 'season_accesses', 'stats', 'stat_metas', 'packageAccesses', 'team', 'teams', 'unassignedGames', 'statisticians', 'issues'));
          } else if ($userType == 'guest') {
            return redirect()->action('[email protected]');
          } else {
            abort(404);
          }
      }

    } else {
      abort(404);
    }
}

which takes us to the home view for a coach in this case. The most important part from that view to see is this:

@include('partials.statTables.statTable', ['tableNumber' => 1, 'timeSpan' => 'season', 'timeSpanId' => $seasonId, 'who' => 'team', 'opponentOption' => 'no'])

which takes us to this partial that I created to display stat tables:

    <div class="container-fluid">
  <div class="row">
    <div class="col">
      <div class="table-responsive">
        @if($who != 'player')
        //Assume true
          <table class="table table-sm">
            <thead>
              <tr>
                <th>#</th>
                <th>Name</th>
                @foreach($stat_metas as $stat_meta)
                  @if($stat_meta->table_group == $tableNumber)
                    <th>{{$stat_meta->stat_abr}}</th>
                  @endif
                @endforeach
              </tr>
            </thead>
            <tbody>
              @foreach($players as $player)
                <tr>
                  @if($game->location == 'home')
                    <td>{{$player->latestJersey->home_number}}</td>
                  @else
                    <td>{{$player->latestJersey->away_number}}</td>
                  @endif
                  <td nowrap><a style="color: #225c92" href="/players/{{$player->id}}">{{$player->name}}</a></td>
                @foreach($stat_metas as $stat_meta)
                  @if($stat_meta->table_group == $tableNumber)
                    <td>{{$stat_meta->statPlacement($timeSpan, $timeSpanId, 'player', $player->id, $opponentOption, true, $stats)}}</td>
                  @endif
                @endforeach
                </tr>
              @endforeach
              <tr>
                <th></th>
                <th>Team Totals</th>
                @foreach($stat_metas as $stat_meta)
                  @if($stat_meta->table_group == $tableNumber)
                    <th>{{$stat_meta->statPlacement($timeSpan, $timeSpanId, 'team', $user->team->id, $opponentOption, true, $stats)}}</th>
                  @endif
                @endforeach
              </tr>
              <?php $opponentStatsEnabled = 'no'; ?>
              @foreach($packageAccesses as $packageAccess)
                @if($packageAccess->team_id == $team->id && $packageAccess->season_id == $game->season_id && $packageAccess->package->package_slug == 'opponentTeamStats')
                  <?php $opponentStatsEnabled = 'yes'; ?>
                @endif
              @endforeach
              @if($opponentStatsEnabled == 'yes')
                <tr>
                  <td></td>
                  <td>Opponent</td>
                  @foreach($stat_metas as $stat_meta)
                    @if($stat_meta->table_group == $tableNumber)
                      <td>{{$stat_meta->statPlacement($timeSpan, $timeSpanId, 'team', $user->team->id, 'just', true, $stats)}}</td>
                    @endif
                  @endforeach
                </tr>
              @endif
              <!--$stat_meta->statPlacement('season', 7, 'team', 1, 'no', true)-->
            </tbody>
          </table>


        @elseif($who == 'player')
        //Assume False

which calls on $stat_meta quite a bit, so let's now include the beast that is Stat_Meta

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\Log;
use App\Models\Stat;
use App\Models\Game;
use App\Models\Team;
use App\Models\Season;
use App\Models\User;
use App\Models\Starters;
use App\Models\Substitution;
use App\Models\Stat_Meta;

class Stat_Meta extends Model
{
    //Table Name
    protected $table = 'stat_metas';

    //Primary Key
    public $primaryKey = 'id';

    //Timestamps
    public $timstamps = true;

    //Add Relationships
    public function stats() {
      return $this->belongsTo('App\Models\Stat', 'stat');
    }

    public function sport() {
      return $this->hasMany('App\Models\Sport');
    }

    //The fun shit starts here

    //All stats go through this function before being sent off so that they can be formatted the way the situation calls for
    public function formatStat($value, $formatted = false, $type = null) {
      if($formatted == true || $formatted == 'yes' || $formatted == 'y') {
        if($type == 'percentage' || $type == '%') {
          return round($value * 100, 1) . '%';
        } elseif($type == 'plusMinus' || $type == 'pm') {
          if($value >= 0) {
            return '+' . round($value, 1);
          } else {
            return round($value, 2);
          }
        } else {
          return round($value, 2);
        }
      } else {
        return $value;
      }
    }

    //Send info off to proper function
    public function statPlacement($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted = false, $stats = null) {

      /**********
      timeSpan options: 'game', 'season', 'null'
      timeSpanId: the exact game you want, or season
      who: 'player', 'team', 'null'
      whoId: which player or team
      opponentOption:
        'yes': include opponent stats
        'no': don't include opponent stats
        'just': just include opponent stats
      formatted: do you want the number rounded and formatted for display
      **********/

      $args = func_get_args();

      if($this->file_safe_name == 'twofgatt' || $this->file_safe_name == 'threefgatt' || $this->file_safe_name == 'ftatt') {
        return $this->attemptException($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->type == "recorded" && $this->aggregation == "sum") {
        return $this->counterFunction($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'twofgpercent' || $this->file_safe_name == 'threefgpercent' || $this->file_safe_name == 'efffgpercentage' || $this->file_safe_name == 'fgpercentage' || $this->file_safe_name == 'ftpercentage'){
        return $this->shootingPercentage($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted, $stats);
      }
      elseif($this->file_safe_name == 'astto') {
        return $this->assistToTurnover($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'fgatt' || $this->file_safe_name == 'fgmade') {
        return $this->fieldGoalAggs($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'reb') {
        return $this->reboundAddition($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'point') {
        return $this->pointsFunction($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'ppsatt') {
        return $this->pointsPerShotAttempt($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'minutes') {
        return $this->minutes($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'astrate') {
        return $this->astrate($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'plusminus') {
        return $this->plusMinus($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
      elseif($this->file_safe_name == 'usageratio') {
        return $this->usageRatio($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      }
    }

    //!!!!!!!!!!!!!!
    //Stat functions
    //!!!!!!!!!!!!!!
    public function attemptException($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      if($this->file_safe_name == 'twofgatt') {
        $missed = Stat::where('stat', 2)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $made = Stat::where('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      }
      elseif($this->file_safe_name == 'threefgatt') {
        $missed = Stat::where('stat', 5)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $made = Stat::where('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      }
      elseif($this->file_safe_name == 'ftatt') {
        $missed = Stat::where('stat', 19)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $made = Stat::where('stat', 20)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      }

      return $this->formatStat($missed + $made);
    }

    public function counterFunction($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      return $this->hasMany('App\Models\Stat', 'stat')->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
    }

    public function shootingPercentage($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted, $stats) {
      if($this->file_safe_name == 'twofgpercent') {
        $numerator = Stat::where('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $denominator = Stat::where('stat', 2)->orwhere('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      } elseif($this->file_safe_name == 'threefgpercent') {
        $numerator = Stat::where('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $denominator = Stat::where('stat', 5)->orwhere('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      } elseif($this->file_safe_name == 'efffgpercentage') {
        $numerator = (Stat::where('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count() * 1.5) + Stat::where('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $denominator = Stat::where('stat', 2)->orwhere('stat', 3)->orwhere('stat', 5)->orwhere('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      } elseif($this->file_safe_name == 'fgpercentage') {
        $numerator = Stat::where('stat', 3)->orwhere('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $denominator = Stat::where('stat', 2)->orwhere('stat', 3)->orwhere('stat', 5)->orwhere('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      } elseif($this->file_safe_name == 'ftpercentage') {
        $numerator = Stat::where('stat', 20)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
        $denominator = Stat::where('stat', 19)->orwhere('stat', 20)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      }
      if($denominator <= 0 && $numerator > 0) {
        return ('∞');
      } elseif($denominator == 0 && $numerator == 0) {
        return ("-");
      } else {
        return $this->formatStat($numerator / $denominator, $formatted, '%');
      }
    }

    public function assistToTurnover($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted){
      $assists = Stat::where('stat', 7)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $turnovers = Stat::where('stat', 30)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();

      if($turnovers <= 0 && $assists > 0) {
        return ('∞');
      } elseif($turnovers == 0 && $assists == 0) {
        return ("-");
      } else {
        return $this->formatStat($assists / $turnovers, $formatted, null);
      }
    }

    public function fieldGoalAggs($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $twoMissed = Stat::where('stat', 2)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $twoMade = Stat::where('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $threeMissed = Stat::where('stat', 5)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $threeMade = Stat::where('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();

      if($this->file_safe_name == 'fgatt') {
        return $this->formatStat($twoMissed + $twoMade + $threeMissed + $threeMade, $formatted, null);
      } elseif($this->file_safe_name == 'fgmade') {
        return $this->formatStat($twoMade + $threeMade, $formatted, null);
      }
    }

    public function reboundAddition($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $o = Stat::where('stat', 22)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $d = Stat::where('stat', 13)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();

      return $this->formatStat($o + $d, $formatted, null);
    }

    public function pointsFunction($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $twoMade = Stat::where('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $threeMade = Stat::where('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $ftMade = Stat::where('stat', 20)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();

      return $this->formatStat(($twoMade * 2) + ($threeMade * 3) + $ftMade, $formatted, null);
    }

    public function pointsPerShotAttempt($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $points = $this->pointsFunction($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      $twoMissed = Stat::where('stat', 2)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $twoMade = Stat::where('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $threeMissed = Stat::where('stat', 5)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $threeMade = Stat::where('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $shotAttempts = $twoMissed + $twoMade + $threeMissed + $threeMade;

      if($shotAttempts <= 0 && $points > 0) {
        return ('∞');
      } elseif($shotAttempts == 0 && $points == 0) {
        return ("-");
      } else {
        return $this->formatStat($points/$shotAttempts, $formatted, null);
      }
    }

    public function astrate($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $astTwoMade = Stat::where('stat', 3)->where('assisted', 'Yes')->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $astThreeMade = Stat::where('stat', 6)->where('assisted', 'Yes')->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $twoMade = Stat::where('stat', 3)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();
      $threeMade = Stat::where('stat', 6)->settings($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted)->count();

      $assistedFieldGoals = $astTwoMade + $astThreeMade;
      $fieldGoals = $twoMade + $threeMade;

      if($fieldGoals <= 0 && $assistedFieldGoals > 0) {
        return ('∞');
      } elseif($fieldGoals == 0 && $assistedFieldGoals == 0) {
        return ("-");
      } else {
        return $this->formatStat($assistedFieldGoals / $fieldGoals, $formatted, '%');
      }
    }

    public function singleGameMinutes($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $minutes = 0;
      $player = User::find($whoId);
      $game = Game::find($timeSpanId);
      $starters = $game->hasMany('App\Models\Starters', 'game_id')->first();
      if($game->stats_done === "yes") {
        $subs = Substitution::where('game_id', $timeSpanId)->orderBy('video_timestamp', 'asc')->get();
        //If no subs give all mins to starters
        if(count($subs) <= 0) {
          if($whoId == $starters->st_1 || $whoId == $starters->st_2 || $whoId == $starters->st_3 || $whoId == $starters->st_4 || $whoId == $starters->st_5) {
            $minutes = $game->periods * $game->period_length;
          }
        } else {
          //Add the time for starters before first sub
          if($whoId == $starters->st_1 || $whoId == $starters->st_2 || $whoId == $starters->st_3 || $whoId == $starters->st_4 || $whoId == $starters->st_5) {
            $minutes += (($subs->first()->period - 1) * ($this->period_length)) + ($game->period_length - $subs->first()->clockTime);
            //Log::debug("Starter time for player " . $player->name . ": " . ((($subs->first()->period - 1) * ($this->period_length)) + ($game->period_length - $subs->first()->clockTime)));
          }
          $subArray = [];
          foreach($subs as $sub) {
            array_push($subArray, [$sub->period, $sub->clockTime]);
            //Log::debug("Period: " . $sub->period . " | ClockTime: " . $sub->clockTime);
          }

          //Log::debug($subArray);

          //Handle each sub after the starting lineup
          foreach($subs as $index=>$sub) {
            //If it is the last sub
            if($index + 1 == count($subs)) {
              if($whoId == $sub->lineup_1 || $whoId == $sub->lineup_2 || $whoId == $sub->lineup_3 || $whoId == $sub->lineup_4 || $whoId == $sub->lineup_5) {
                $minutes += ($game->periods * $game->period_length) - (($sub->period - 1) * ($game->period_length)) - $sub->clockTime;
                //Log::debug("Last sub time for player " . $player->name . ": " . (($game->periods * $game->period_length) - (($sub->period - 1) * ($game->period_length)) - $sub->clockTime));
              }
            //Every other middle-sub
            } else {
              if($whoId == $sub->lineup_1 || $whoId == $sub->lineup_2 || $whoId == $sub->lineup_3 || $whoId == $sub->lineup_4 || $whoId == $sub->lineup_5) {
                //$minutes += ((($subArray[$index + 1][0] - 1) * $this->period_length) + ($subArray[$index + 1][1])) - (($sub->period - 1) * $this->period_length);
                $minutes += $game->getFullClockTime($subArray[$index + 1][0], $subArray[$index + 1][1]) - $game->getFullClockTime($sub->period, $sub->clockTime);
                //Log::debug("Sub " . ($index + 1) . " time for player " . $player->name . ": " . ($game->getFullClockTime($subArray[$index + 1][0], $subArray[$index + 1][1]) - $game->getFullClockTime($sub->period, $sub->clockTime)) );
              }
            }
          }
        }
      }
      return $minutes;
    }


    public function minutes($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $minutes = 0;
      if($who === "player") {
        if($timeSpan === "game") {
          $minutes = $this->singleGameMinutes($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
        } elseif($timeSpan === "season") {
          $player = User::find($whoId);
          $team = $player->team;
          $games = Game::where('season_id', $timeSpanId)->where('team_id', $team->id)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $totalNow = $minutes;
            $minutes += $this->singleGameMinutes('game', $game->id, $who, $whoId, $opponentOption, $formatted);
            //Log::debug($player->name . " played: " . ($minutes - $totalNow) . " minutes during game against " . $game->opponent);
          }
        } else {
          $player = User::find($whoId);
          $team = $player->team;
          $games = Game::where('team_id', $team->id)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $minutes += $this->singleGameMinutes('game', $game->id, $who, $whoId, $opponentOption, $formatted);
          }
        }
      } elseif($who === "team") {
        if($timeSpan === "game") {
          $game = Game::find($timeSpanId);
          $minutes = $game->periods * $game->period_length * 5;
        } elseif($timeSpan === "season") {
          $games = Game::where('season_id', $timeSpanId)->where('team_id', $whoId)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $minutes += $game->periods * $game->period_length * 5;
          }
        } else {
          $games = Game::where('team_id', $whoId)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $minutes += $game->periods * $game->period_length * 5;
          }
        }
      } else {
        //$who is null here so we are getting stats for everyone.
        if($timeSpan === "game") {
          $game = Game::find($timeSpanId);
          $minutes = $game->periods * $game->period_length * 5;
        } elseif($timeSpan === "season") {
          $games = Game::where('season_id', $timeSpanId)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $minutes += $game->periods * $game->period_length * 5;
          }
        } else {
          $games = Game::where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $minutes += $game->periods * $game->period_length * 5;
          }
        }
      }
      return $minutes;
    }

    public function singleGamePlusMinus($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $plusMinus = 0;
      $player = User::find($whoId);
      $game = Game::find($timeSpanId);
      $starters = $game->hasMany('App\Models\Starters', 'game_id')->first();

      if($game->stats_done === "yes" && $starters != null) {
        $subs = Substitution::where('game_id', $timeSpanId)->orderBy('video_timestamp', 'asc')->get();
        if(count($subs) <= 0) {
          if($whoId == $starters->st_1 || $whoId == $starters->st_2 || $whoId == $starters->st_3 || $whoId == $starters->st_4 || $whoId == $starters->st_5) {
            //if no subs, any player who started will get +/- equal to the games overall differential
            $plusMinus = $game->team_score - $game->opponent_score;
          }
        } else {
          if($whoId == $starters->st_1 || $whoId == $starters->st_2 || $whoId == $starters->st_3 || $whoId == $starters->st_4 || $whoId == $starters->st_5) {
            $plusMinus += ($subs->first()->teamScore - 0) - ($subs->first()->opponentScore - 0);
          }

          $subArray = [];
          foreach($subs as $sub) {
            array_push($subArray, [$sub->teamScore, $sub->opponentScore]);
          }

          //Log::debug($subArray);

          //Handle each sub after the starting lineup
          foreach($subs as $index=>$sub) {
            if($whoId == $sub->lineup_1 || $whoId == $sub->lineup_2 || $whoId == $sub->lineup_3 || $whoId == $sub->lineup_4 || $whoId == $sub->lineup_5) {
              //If it is the last sub
              if($index + 1 == count($subs)) {
                $plusMinus += ($game->team_score - $sub->teamScore) - ($game->opponent_score - $sub->opponentScore);
              } else {
                $plusMinus += ($subArray[$index + 1][0] - $sub->teamScore) - ($subArray[$index + 1][1] - $sub->opponentScore);
              }
            }
          }
        }
      }
      return $plusMinus;
    }

    public function plusMinus($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $plusMinus = 0;
      if($who === "player") {
        $player = User::find($whoId);
        if($timeSpan === "game") {
          $plusMinus = $this->singleGamePlusMinus($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
        } elseif($timeSpan === "season") {
          $games = Game::where('season_id', $timeSpanId)->where('team_id', $player->team->id)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $plusMinus += $this->singleGamePlusMinus('game', $game->id, $who, $whoId, $opponentOption, $formatted);
          }
        } else {
          $games = Game::where('team_id', $whoId)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $plusMinus += $this->singleGamePlusMinus('game', $game->id, $who, $whoId, $opponentOption, $formatted);
          }
        }
      } elseif($who === "team") {
        $team = Team::find($whoId);
        if($timeSpan === "game") {
          $game = Game::find($timeSpanId);
          $plusMinus = $game->team_score - $game->opponent_score;
        } elseif($timeSpan === "season") {
          $games = Game::where("season_id", $timeSpanId)->where('team_id', $team->id)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $plusMinus += $game->team_score - $game->opponent_score;
          }
        } else {
          $games = Game::where('team_id', $team->id)->where('stats_done', 'yes')->get();
          foreach($games as $game) {
            $plusMinus += $game->team_score - $game->opponent_score;
          }
        }
      } else {
        //Not filled in yet because DAMN that would take some processing horsies and I can't think of a reasonable time that I'd need system-wide plus-minus. It just wouldn't really be helpful
        if($timeSpan === "game") {

        } elseif($timeSpan === "season") {

        } else {

        }
      }
      return $this->formatStat($plusMinus, $formatted, 'plusMinus');
    }


    //****************************


    public function singleGameUsageRatio($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted){
      $usageRatio = 0;
      $game = Game::find($timeSpanId);
      $gameStats = $game->stats();
      $subs = $game->subs()->orderBy('video_timestamp', 'asc')->get();
      $starters = $game->hasMany('App\Models\Starters', 'game_id')->first();
      $subArray = [];
      foreach($subs as $sub) {
        array_push($subArray, $sub->video_timestamp);
      }

      //Numerator is constant
      $playerFGA = Stat_Meta::where('file_safe_name', 'fgatt')->first()->statPlacement($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      $playerFTA = Stat_Meta::where('file_safe_name', 'ftatt')->first()->statPlacement($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      $playerTO = Stat_Meta::where('file_safe_name', 'turnover')->first()->statPlacement($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      $teamTotalMins = ($game->periods * $game->period_length) * 5;
      $numerator = (($playerFGA) + 0.44 * ($playerFTA) + ($playerTO)) * ($teamTotalMins);

      //Define denominator variables
      $teamFGA = 0;
      $teamFTA = 0;
      $teamTO = 0;
      $playerTotalMins = Stat_Meta::where('file_safe_name', 'minutes')->first()->statPlacement($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted);
      $denominator = 0;

      //Add things if player was a starter
      if($whoId == $starters->st_1 || $whoId == $starters->st_2 || $whoId == $starters->st_3 || $whoId == $starters->st_4 || $whoId == $starters->st_5) {
        if(count($subArray) > 0) {
          $maxTimeStamp = $subArray[0];
        } else {
          $maxTimeStamp = 9999999999999999999999999999999999999;
        }
        $teamFGA += Stat::where('video_timestamp', "<", $maxTimeStamp)->where('game_id', $game->id)->where(function($query) {
          $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
        })->where(function($query) {
          $query->where('stat', 2)->orWhere('stat', 3)->orWhere('stat', 5)->orWhere('stat', 6);
        })->count();

        $teamFTA += Stat::where('video_timestamp', "<", $maxTimeStamp)->where('game_id', $game->id)->where(function($query) {
          $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
        })->where(function($query) {
          $query->where('stat', 16)->orWhere('stat', 17);
        })->count();

        $teamTO += Stat::where('video_timestamp', "<", $maxTimeStamp)->where('game_id', $game->id)->where(function($query) {
          $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
        })->count();
      }

      if(count($subArray) > 0) {
        foreach($subs as $index=>$sub) {
          //Add things if player was in with the last group
          if($index + 1 == count($subArray)) {
            $minTimeStamp = $subArray[count($subArray) - 1];
            $teamFGA += Stat::where('video_timestamp', ">", $minTimeStamp)->where('game_id', $game->id)->where(function($query) {
              $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
            })->where(function($query) {
              $query->where('stat', 2)->orWhere('stat', 3)->orWhere('stat', 5)->orWhere('stat', 6);
            })->count();

            $teamFTA += Stat::where('video_timestamp', ">", $minTimeStamp)->where('game_id', $game->id)->where(function($query) {
              $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
            })->where(function($query) {
              $query->where('stat', 16)->orWhere('stat', 17);
            })->count();

            $teamTO += Stat::where('video_timestamp', ">", $minTimeStamp)->where('game_id', $game->id)->where(function($query) {
              $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
            })->count();
          } else {
            $maxTimeStamp = $subArray[$index + 1];
            $minTimeStamp = $subArray[$index];
            $teamFGA += Stat::where('video_timestamp', ">", $minTimeStamp)->where('video_timestamp', "<", $maxTimeStamp)->where('game_id', $game->id)->where(function($query) {
              $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
            })->where(function($query) {
              $query->where('stat', 2)->orWhere('stat', 3)->orWhere('stat', 5)->orWhere('stat', 6);
            })->count();

            $teamFTA += Stat::where('video_timestamp', ">", $minTimeStamp)->where('video_timestamp', "<", $maxTimeStamp)->where('game_id', $game->id)->where(function($query) {
              $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
            })->where(function($query) {
              $query->where('stat', 16)->orWhere('stat', 17);
            })->count();

            $teamTO += Stat::where('video_timestamp', ">", $minTimeStamp)->where('video_timestamp', "<", $maxTimeStamp)->where('game_id', $game->id)->where(function($query) {
              $query->where('opponentTeamStat', 'no')->orWhere('opponentTeamStat', NULL);
            })->count();
          }
        }
      }

      //Pull the denominator together
      $denominator = (($teamFGA) + 0.44 * ($teamFTA) + $teamTO) * 5 * ($playerTotalMins);

      if($denominator > 0) {
        $usageRatio = $numerator / $denominator;
      } else {
        $usageRatio = 0;
      }
      return $usageRatio;
    }

    public function usageRatio($timeSpan, $timeSpanId, $who, $whoId, $opponentOption, $formatted) {
      $usageRatio = 0;
      if($who === "player") {
        $player = User::find($whoId);
        if($timeSpan === "game") {
          $usageRatio = $this->singleGameUsageRatio('game', $timeSpanId, 'player', $whoId, false, $formatted);
        } elseif($timeSpan === "season") {
          $games = Game::where('season_id', $timeSpanId)->where('team_id', $player->team->id)->where('stats_done', 'yes')->get();
          $totalGamesPlayed = $games->count();
          $totalUsageAcrossGames = 0;
          foreach($games as $game) {
            $totalUsageAcrossGames += $this->singleGameUsageRatio('game', $game->id, $who, $whoId, $opponentOption, $formatted);
          }
          if($totalGamesPlayed > 0) {
            $usageRatio = $totalUsageAcrossGames / $totalGamesPlayed;
          } else {
            $usageRatio = 0;
          }
        } else {

        }
        return $this->formatStat($usageRatio, $formatted, 'percentage');
      } else {
        return "N/A";
      }
    }
}

hunterhawley left a reply on Query Efficiency

@tray2 I can share it, but because there are so many different queries, I would have to share 2 full documents with 20ish large functions. Let me know if you'd still want me to

13 Sep
1 week ago

hunterhawley started a new conversation Query Efficiency

I am calculating basketball stats and have the models Stat, User (which the basketball players are held within), Team, Stat_Meta, Game, Season, Substitution.

I have a view called statTable that can be added to any other view on the app. statTable basically just iterates through each player on the team and retrieves the calculation for each stat type (found in Stat_Meta model). Within those calculation, there are queries run for the Stat, Game, Season, etc. tables. By the time it iterates through every player and all their stats, we are looking at like 500 queries PER game (often we are going through like ~30 queries/view, so you do the math, it's bad).

My question: With the Laravel debug bar installed, I can see that in my test environment, I've got 3,116 queries running when loading the front page, and 2,432 of them are duplicates. It takes forever to load as well. So, how can I re-work this system of queries to reduce the number of them?

Full disclosure, I'm not a CS person, so this isn't something I'm trained in. Right now, I'm super happy this even works, but now it is going to cost me an arm and a leg to do all these queries at scale (not to mention horrible UX).

I've thought about passing a collection of all Stats that is used all around each part of the calculations, but when I try to do that, I get an error, because I utilize a method on the Stat model during these calculations. If anyone knows of a way to just pass this collection around, but still allow it to use the Stat model methods, that would probably increase the efficiency enough to make this thing work correctly.

09 Sep
2 weeks ago

hunterhawley left a reply on Using Related Model True/false Functions In Eloquent Queries

I may be misunderstanding the article but Im not seeing the connection. Obviously not saying there isnt one there haha, Im just having trouble seeing how it applies

hunterhawley started a new conversation Using Related Model True/false Functions In Eloquent Queries

Howdy!

I've got three models: Game, User, and Game_Assignment

The users are assigned games to watch through the Game_Assignment Model. Games can have multiple Game_Assignment's, and Users can too.

For each game, the most recent Game_Assignment is the one that should be used, the others are just for reference sake. This is because a game might be assigned to a user and it might need to be re-assigned for whatever reason. Users have multiple Game_Assignments because they may have multiple games to watch.

Now, I am trying to get a count of Game's assigned for each User, but only if the assignment is the most recent one for that game.

On User, I have the following function:

public function current_game_assignments() {
  return $this->hasMany('App\Models\Game_Assignment', 'statistician_id')->where('isLatestAssignment', true);
}

"isLatestAssignment" comes from Game_Assignment, and here is that function:

public function isLatestAssignment() {
  if($this->game->latestAssignment()->id == $this->id) {
    return true;
  } else {
    return false;
  }
}

Finally, "latestAssignment" comes from Game, and here is that one too:

public function latestAssignment() {
  return $this->hasOne('App\Models\Game_Assignment', 'game_id')->latest();
}

I know User is causing the problem, because Eloquent thinks "isLatestAssignment" is a column, not a function.

Here is the error I am getting:

Column not found: 1054 Unknown column 'isLatestAssignment'

Any ideas on how I can achieve this?

07 Sep
2 weeks ago

hunterhawley left a reply on Best Approach To Pre-loading Query-intense Calculations

Not to mention my hosting costs are going to get crazy here soon. Yeah... this is the right way to go right now haha.

hunterhawley left a reply on Best Approach To Pre-loading Query-intense Calculations

I may just try and get the efficiency up and see if maybe that does help anything. I figured it wouldn't, but now that I am considering how many queries I am making (for a team of 15, and a season of about 30 games, it's about 13,000ish) I should cut that down and worry about this if it still isn't sped up. Thanks!

hunterhawley left a reply on Best Approach To Pre-loading Query-intense Calculations

This page from the NBA shows a couple tables that demonstrate what I am making at the end of this: https://stats.nba.com/game/0021801225/

Essentially, I want to store this table in the DB, but for a bunch of teams, players, and games. I can't make a new table for each team, player, or whatever. It would be a LOT of tables.

hunterhawley left a reply on Best Approach To Pre-loading Query-intense Calculations

Sure. A good example is the assist stat.

In the Stat_Meta table, there is a record for Assist. It basically just has an ID, the name of it (Assist), abbreviation, description, etc.

Let's say the assist stat_meta ID is 3.

In the Stats table, you have a column called "Stat". This is where, if you are trying to record that an assist happened (the point of this app is you watch basketball film and record when things happen in the game), you would place a 3.

So, with that you have a list of different stats that took place all in a table called Stats. These relate to their Stat_Meta, which "decodes" what a 3 means (in this case, assist).

Additionally, each Stat is related to a User (a player) and a Game. Basically, Users (players) have Stats recorded for them within Games.

So, when I am calculating a teams stats for a game, I list off all the players names in a table in the view, and then sum all of the Stat table rows that fit the right criteria (was it the right player, does it match the game id, does it match the stat type I am asking for, etc).

However, the calculations themselves get a bit more complex than just summing stats, so that is why I am trying to do all of this. I'll find an example online because I can't attach screenshots here I don't think.

hunterhawley started a new conversation Best Approach To Pre-loading Query-intense Calculations

Hey y'all,

I've got an interesting optimization problem that I'd like some thoughts on. I have two tables in question here: Stats and Stat_Metas. We are talking about basketball stats here, in case that helps anyone visualize this.

Stat_Metas is a list of the types of stats I am recording on the system (assists, rebounds, etc.).

Stats are where I record those stats happening. So, the Stats table relates to one Stat_Meta, but also tells you which player did the assist or shot or whatever.

Now, I calculate (aggregate, really) a few stats that are really rather intense and are taking up to 15-20 seconds to load in the view. The stats are calculated on one page for every player a team has, and every game they play. and these numbers are all aggregated into a table where the rows are player's names, and the columns are the types of stats I am calculating.

I fully recognize that part of the reason for this is that I have somewhat inefficient code (my CS-student roommate used his knowledge from a data structures class to tell me that much), but I have a feeling that even after fully optimizing it, I won't be at a good speed to hot-load these calculations.

So, I'm thinking that I'd like to write a queue worker to run every time the data changes that will do these calculations one time, and be available whenever any user wants to see them. Compare that to my current system where as the users requests the page, they have to wait for the stats to be aggregated and calculated.

Here is the question:

How should I do this in the DB?

I am considering using JSON in the DB because I can relate these calculated numbers to each player for an unknown number of players on each team.

I am also considering giving each stat type it's own column in a table, and relating that to the players ID. Problem there is that if I add new stats in the future, it will likely be a headache.

What do you all think? Are either of these the right approach? Is this the right approach at all?

17 Aug
1 month ago

hunterhawley left a reply on Dynamic Property Call Giving Error (Property [game] Does Not Exist On The Eloquent Builder Instance.)

I kept returning 0 records when I had it set to where('stats_done', '!='. 'yes') but who knows. I appreciate the help, as always!

hunterhawley left a reply on Dynamic Property Call Giving Error (Property [game] Does Not Exist On The Eloquent Builder Instance.)

I ended up getting this to work. Turns out I didn't realize that != would not get me null values, so I had to account for that

$gamesDue = Game_Assignment::query()
          ->where('statistician_id', $statistician->id)
          ->whereHas('game', function (Builder $query) {
            $query->whereNull('stats_done')->orWhere('stats_done', 'no');
          })
          ->count();

hunterhawley started a new conversation Dynamic Property Call Giving Error (Property [game] Does Not Exist On The Eloquent Builder Instance.)

Hey there!

I have two models: Game and Game_Assignment. Game_Assignment tells whose job it is to play a game.

I am trying to count the number of Game_Assignment's that a user has their id on that also have a specific value on the Game model that it relates to. I'll just get into the Models/the code

Game Model Relationship:

public function assignments() {
  return $this->hasMany('App\Models\Game_Assignment', 'game_id');
}

Game_Assignment Relationship:

public function game() {
  return $this->belongsTo('App\Models\Game', 'game_id');
}

Where things are going wrong (in a queue job, if that makes a difference)

$gamesDue = Game_Assignment::where('statistician_id', $statistician->id)->game->where('stats_done', '!=', 'yes')->count();

I have also tried the following two things, neither worked:

$gamesDue = Game_Assignment::where('statistician_id', $statistician->id)->game()->where('stats_done', '!=', 'yes')->count();

and...

$gamesDue = Game_Assignment::where('statistician_id', $defaultStatistician->id)->with(['games' => function($query) {
        $query->where('stats_done', '!=', 'yes');
      }])->count();

None of these work, and the first one I showed threw an error:

Property [game] does not exist on the Eloquent builder instance.

Anyone have an idea of where I am going wrong? I am using this link as my reference https://laravel.com/docs/5.8/eloquent-relationships#eager-loading

14 Aug
1 month ago

hunterhawley left a reply on The Process Exceeded The Timeout Of 600 Seconds. (Symfony)

Just tried again after changing the php.ini and restarting with no luck. After seeing the max_execution_time => 0 though I assumed that might happen. Any other thoughts?

hunterhawley left a reply on How "smart" Should Model Methods Be?

I agree. I am doing a similar thing, I have admins, coaches, players, etc. and they all exist on the same table. I am also newer to Laravel but things have worked well for me doing it this way. However, if this isn't the correct method, I'd appreciate someone a bit more experienced than me jumping in.

hunterhawley left a reply on The Process Exceeded The Timeout Of 600 Seconds. (Symfony)

This is the response I get

max_execution_time => 0 => 0

hunterhawley left a reply on The Process Exceeded The Timeout Of 600 Seconds. (Symfony)

In the meantime, I am now noticing on my Queue Worker log something about both a $this-timeout and $this->idleTimeout

Could idleTimeout be tripping me up?

hunterhawley left a reply on The Process Exceeded The Timeout Of 600 Seconds. (Symfony)

I just checked and that is set to 180, so it seems weird that it would have run for 600 with that setting. I will give it a try and get back to you though. Thanks!

hunterhawley started a new conversation The Process Exceeded The Timeout Of 600 Seconds. (Symfony)

I have a queue worker that I am running to process some video, and it takes a while.

When trying to work with a large video, I get the following error:

local.ERROR: The process "*long command*" exceeded the timeout of 600 seconds. {"exception":"[object] (Symfony\Component\Process\Exception\ProcessTimedOutException(code: 0): The process \"*long command*" exceeded the timeout of 600 seconds. at /home/hunterhawley/blueprintstats/vendor/symfony/process/Process.php:1237)

The thing is though, in my queue worker, I have:

--timeout=1200

Which is exactly double the 600 second timeout that the error claims is tripping it up. It is obvious to me that either there is some cap at 600, or that some other setting is preventing it from going above. Does anyone have a guess as to which setting may be capping me at 600?

13 Aug
1 month ago

hunterhawley left a reply on Comparing Dates In Where Clause (Twist: Date Stored At String)

Problem was totally unrelated and was a dumb mistake by me. Please ignore this! Thanks

hunterhawley started a new conversation Comparing Dates In Where Clause (Twist: Date Stored At String)

I've got a model that has a column: date_revoked (this is stored as a string)

After this date is passed, a user is no longer supposed to be able to access a certain page.

I'd like to restrict this access in the controller.

Currently, I can do a janky partial fix by putting this inside of a foreach in my view

@if(strtotime($filmShareIn->date_revoked) >= strtotime(date('Y-m-d')))

But when I try the following in the controller, I get no filtering:

->where('date_revoked', '>=', Carbon::now())

I know I may have really just screwed up by storing it as a string, but if anyone can get me out of this jam, it is my friends at Laracasts.

Thanks!

12 Aug
1 month ago

hunterhawley left a reply on Check If Submitted Email Is Unique

Thanks. This was Plan b but I didn't know if there was a function that did that for you.

hunterhawley started a new conversation Check If Submitted Email Is Unique

Hey y'all!

I've got my user table set up to make all emails be unique, but if a user tries to create a guest account for someone that is already in the system, I'd like it to not create a new user in the Controller, but to just add a record to a separate table.

How in the controller can I say "if $request->input('email') is not unique"?

Thanks in advance!

31 Jul
1 month ago

hunterhawley started a new conversation Count Objects In Related Model If Condition In Third Model Is Met

I've got five models:

Team

Season

Game

Stat

Stat_Meta

Teams have seasons, seasons have games, and games have stats, and each stat relates to a stat_meta. The stat_meta table is basically static, and it's there to classify stats by type.

I am working on a function in the Stat_Meta table so that I can call for any particular stat type (or stat_meta) to be counted within the confines of a given game, season, or team.

This is really easy for games, because I just do this:

return $this->hasMany('App\Models\Stat', 'stat')->where('game_id', 1)->count(); However, since stats are not directly related to a season or team (they are related by the game being related to both) that gets really hairy.

I know this is wrong, but I tried it (it failed) to maybe make it clearer what I am trying to do here:

return $this->hasMany('App\Models\Stat', 'stat')->where('game.season_id', 7)->count(); Doing this gave me the following error (somewhat obviously):

Column not found: 1054 Unknown column 'game.season_id' in 'where clause'

After a lot of Googling I have yet to really find anything out about this. Any thoughts?

30 Jul
1 month ago

hunterhawley started a new conversation Call Model Function In "the Plural" Sense?

Hey y'all!

Quick background:

I have three models that are working together here: Seasons, Games, and Stats.

Seasons will have multiple games, Games will have multiple stats.

On the Stats model, I've created a function that I'm trying to make do a lot. I want this function to take 6 arguments.

  1. The type of stat. Easy to get, just have to use a where()
  2. The aggregation level. Basically, do I want to get all the stats inside a game, or inside a season. This one took me a while to make work, but I got it done.
  3. The exact aggregation point. Say I want to aggregate by game in the last arg. In this one, I'll put the ID of the game that I want to see the stats for.

4, 5, and 6 are irrelevant to this exact issue.

So, I can make all of this work, but there is a catch. I have to do this:

@foreach($stats as $stat)

 {{$stat->statPlacement(2, 'game', 1, 'team', 9, $formatted = false)}}

@endforeach

I'd like not to have to use a foreach. Essentially, this function goes and counts every time this particular type of stat happens inside the parameters of the given aggregation. That being said, if I use a foreach, I am just returning the same number a whole lot of times. I just want it once.

I'm probably approaching this problem wrong, and I may not even need to do this in the model, but let me know what you think.

Thanks y'all!

Hunter

06 Jul
2 months ago

hunterhawley left a reply on Memory Exhaustion, Big Video Files Need Uploaded

Really? Im using FFMpeg through a Symfony Process and it is working great. It took a good bit of configuring but it worked. My only issue with Vimeo is that Im not sure how much customization I can really do.

hunterhawley left a reply on Memory Exhaustion, Big Video Files Need Uploaded

Well, currently I'm having users upload their video files directly to my server so that I can perform FFMpeg transformations on the videos. Is this a bad idea? Should I just upload directly to S3 and then have FFMpeg download them back for each transformation?

hunterhawley started a new conversation Memory Exhaustion, Big Video Files Need Uploaded

Hey y'all!

I've got to get some (potentially) very large files uploaded to my S3 bucket on a Laravel Job I am building out. I am getting the dreaded "Allowed memory size of ### bytes exhausted" error, and I have no interest in increasing the memory limit in php.ini (simply because I don't know how large some of these files will go, and at some point I need to quit running away from these large files by increasing memory_limit to ridiculous levels).

The question is: Does Laravel make chunking this thing easy? Is there a function I am not seeing that I can use?

I know the answer is probably no, but Laravel makes SO many things easy for me, I figured I might ask to see if I was missing something in my Google's.

If this does not exist in Laravel, what should I do? I know that I need to take the file into memory a chunk at a time, but I have no idea where to start on that.

Thanks!

03 Jul
2 months ago

hunterhawley left a reply on Access Relationship From Inside A Where Clause (or However I'm Supposed To Do This)

I think you are on the right track, but I'll have to keep working on this one. I've got a lot going on in my model that needs to be wrangled haha

02 Jul
2 months ago

hunterhawley started a new conversation Access Relationship From Inside A Where Clause (or However I'm Supposed To Do This)

Hey y'all! Quick question this morning. I've got two tables: 'stats' and 'stat_metas'. 'stat_metas' holds all the 'meta' information about a stat, like its name, description, that stuff. 'stats' holds actual instances of stats, referenced back to stat_meta (each stat has one stat_meta, each stat_meta has many stats).

I have another model though called Game, where I am working with these two tables. (Game has many stats, you get the picture)

From Game, I am using this to pull the number of times a stat with the stat_meta id of 3 comes up:

$twosMade = $this->hasMany('App\Models\Stat', 'game_id')->where('stat', '3')->count();

Note: 'stat' (as in where('stat', '3')) is the column name in the 'stats' table that references the id on the stat_metas table)

However, instead of using the id from stat_meta, I would like to use the name of the stat. I want to do something like this, however this is obviously wrong:

$twosMade = $this->hasMany('App\Models\Stat', 'game_id')->where('stat->stat_meta->stat_name', 'assist')->count();

Does anyone have an idea of how this should be approached? I wish I had a visual or something to show, when explaining these Eloquent relationships and whatnot I get pretty confused at times.

hunterhawley left a reply on Queuing File Transformation After File Upload

@JOHNBRAUN - This actually helps a lot. There is a lot to unpack here, so it'll probably take me a bit before I get it running, but I'll post an update on here with any further questions! Thanks a lot John! Nice website too!

hunterhawley left a reply on Should I Use A Helper Function? A Partial View?

@SNAPEY - I was actually asking a friend about this issue yesterday and he told me the same thing. I went ahead and converted everything so that it works in the model now, and whew, it is a LOT nicer now.

01 Jul
2 months ago

hunterhawley started a new conversation Should I Use A Helper Function? A Partial View?

Hi there,

I am building a Laravel application where people upload videos of basketball games, and then stats are recorded. So, in the db, I've got a 'stat_metas' table where I store all the different types of basketball statistics (assists, 2 pointers made, etc). I also have a 'stats' table where I keep every recorded stat. Each record holds which "stat_meta" it was, which player did it, which game it happened in, etc.

Now, I am trying to display all of this data in a table, on both a per-game and per-team (teams have multiple games, so a per-team table would be all of the per-game data, aggregated) basis.

I also have some stats (like free throw percentage) that I had to calculate. I put each of those formulas in separate partial views and @included them (I now fear that was a bad idea, but you tell me).

Now, all of this 'worked' for both the calculated and added up stats, but then came time for me to add a totals row at the bottom of this table, and things got REAL messy, REAL quick, in part because I used the partial views I think.

Does anyone have any idea how I can make this work? Should I try using helper functions for each calculated stat?

hunterhawley left a reply on Queuing File Transformation After File Upload

@JOHNBRAUN - Hey John,

This is super helpful. Now the only questions I have are

  1. How do I get $videos ready to send over to the job? Right now (I'm currently just processing this in the controller and now I am converting it) I can do $request->file('video_file'); and then just loop through each file with a foreach. I can't pass $request though, because I get an error that says "serialization of 'closure' is not allowed laravel job"

  2. So, I could put something in the DB that says "in progress" while the video processing is happening, and then change it to "processed" once it is done? Basically what do you call to make something happen upon successful processing?

Thank you so much!

hunterhawley started a new conversation Queuing File Transformation After File Upload

Hey there! I have been Googling around, and was surprised that I couldn't find the answer to this anywhere. I have a form that has a few text/integer fields, and one multi-select file field for videos. Currently, I've got code that will upload everything, put the text fields in the DB, then use FFMPEG to compress and combine every video into one concatenated larger video.

All of this works, my problem is that for any video larger than a minute or two, the FFMPEG process takes FOREVER. So, I've been watching the Laracasts about Queuing. I haven't found anything about how to pass $request data, and when I tried, I was told that serialization was not allowed for that. At this point, I just need to know how to pass my video objects to a job where they can be processed, so that the user doesn't have to sit there and wait. Any thoughts?