CODEWITHSUNDEEP
phpmysqllaravelcsveloquent
KSails
KSails

Reputation: 35

Laravel Eloquent Query Optimization

The purpose of my application is to crunch payroll data by department, date(daily, weekly, monthly, quarterly, and annually) and display it in a dashboard with graphs.

I have two(2) tables that I upload CSV to obtain data: 'attendance' and 'payroll';

I have ten(10) schools each identified by a four(4) digit number in a text field.

**attendance** = date | school_code | department_name | child_count

**payroll** = date | school_code | teacher_name | department | hours | pay_rate

Here's what I'm trying. My queries are terribly expensive.

public function index() {

    $schools     = array( '1001', '1003', '1004', '1005', '1007', '1008', '1009', '1010', '1011' );

    $infants   = 'Infants';
    $ones      = 'Ones';
    $twos      = 'Twos';
    $threes    = 'Threes';
    $fours     = 'Fours';
    $fives     = 'Fives';
    $schoolAge = 'School Age';
    $unknowns  = 'Unknown';
    $preK      = 'PreK';
    $office    = 'Office';

    $oneInfants    = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $infants );
    $oneOnes       = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $ones )->get();
    $oneTwos       = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $twos )->get();
    $oneThrees     = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $threes )->get();
    $oneFours      = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $fours )->get();
    $oneFives      = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $fives )->get();
    $oneSchoolAges = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $schoolAge )->get();
    $oneUnknowns   = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $unknowns )->get();
    $onePreK       = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $preK )->get();
    $oneOffice     = ChildCount::where( 'school_code', $schools[0] )->where( 'department_name', $office )->get();


... **REPEATED FOR EACH SCHOOL** ...


    $oneMaster= DB::table( 'payrolls' )
                    ->join( 'child_counts', 'child_counts.school_code', '=', 'payrolls.school_code' )
                    ->where( 'payrolls.department', $infants )
                    ->where('payrolls.school_code',$schools[0])
                    ->take( 100 )
                    ->select( 'payrolls.*', 'child_days' )
                    ->get();

I don't have a Model for schools because this is a third-party application with the data being exported CSV from our Main Operations Program.

How do I optimize these queries while joining on school_code between child_counts && payrolls - once I'm able to iterate through these schools, departments, and dates - I can query daily, weekly, monthly, quarterly, and annually;

Thanks in advance.

Upvotes: 1

Views: 336

Answers (1)

Pepijn Olivier
Pepijn Olivier

Reputation: 977

You will benefit when using an actual Eloquent model for every database table you have (create a model for payrolls if you don't already have one).

Once you have the Payroll model, make sure the appropriate relationship functions are defined on each model. (Make a relationship on ChildCount and to make your life easier later on, also define the reversed relationship on Payroll).

ChildCount.php

public function payrolls() {
    return $this-hasMany(Payroll::class, 'school_code', 'school_code');
}

// I think you can even limit the payrolls in your relationship, not sure about this
public function payrollsLimited() {
    return $this-hasMany(Payroll::class, 'school_code', 'school_code')->limit(100);
}

This way you can leverage eager loading. This should get you started:

$schoolCode = $schools[0];
$departmentNames = [$infants, $ones, $twos, $threes]; // etc.
$childCounts = ChildCount::where( 'school_code', $schoolCode)
  ->whereIn( 'department_name', $departmentNames )
  ->with(['payrolls']) // assumes the ChildCount model has a relationship with the payroll model
  ->get();

The above could be wrapped in a foreach, to loop over each school, but of course you could load all the data for every school in one query as well. I don't know how many schools we're talking, or how big your payrolls table is, but if you'd want to try this, you would use whereIn instead of where.

Good luck!

PS: Last thing I want to add, is you might want to take another look at your database design or model names. It doesn't make sense to me that the payrolls and childcounts tables are joined via school_code (and is ChildCount the best name for your model?)

Upvotes: 1

Related Questions