Query taking far too long - very inefficient please help!

[leaden hull]

Supply the code properly formatted in code blocks

[main stirrup]

protected function hourlyPerformance(Request $request, $line_id = null)
{
    // dataset should be either pulled from the request object or passed in to this method as a parameter
    // valid values are a, p, q, oee, tos, produced or waste. Default to produced if an invalid value is given.
    // $dataset = $request->dataset ?? 'produced';
    $percentages = ['a', 'p', 'q', 'oee', 'tos'];
    $datasets = ['produced', 'waste', 'tos', 'a', 'p', 'q', 'oee'];

    // set pagination amount
    $limit = $request->limit ?? config('app.pagination_limit');

    // set start filters if not set
    if(empty(array_filter($request->except('module'))))
    {
        // this will force the quick filter system to display todays data unless overruled.
        $request->merge(['quick-filter' => 'today']);
    }

    $table_data = array();
    foreach ($datasets as $dataset) {
        // get individual line data
        $line_data = $this::join('lines', 'oee_data.line_id', 'lines.id')
        ->join('sites', 'lines.site_id', 'sites.id')

        // produced
        ->when($dataset === 'produced', function ($q) {
            return $q->join('product_logs', 'oee_data.id', 'product_logs.oee_data_id')
            ->selectRaw('sum(produced) as output, break_even_target as target');
        })

        // waste
        ->when($dataset === 'waste', function ($q) {
            return $q->join('waste_logs', 'oee_data.id', 'waste_logs.oee_data_id')
            ->join('product_logs', 'oee_data.id', 'product_logs.oee_data_id')
            ->selectRaw('sum(waste_logs.quantity) as output, ((product_logs.produced / 100) * (100 - lines.target_q)) as target');
        })

        // turn of scale % - leave as is for now but this will need further work once I gather information and run additional tests
        ->when($dataset === 'tos', function ($q) {
            return $q->join('product_logs', 'oee_data.id', 'product_logs.oee_data_id')
            ->selectRaw('turn_of_scale as output, 0 as target');
        })

        // availability %
        ->when($dataset === 'a', function ($q) {
            return $q->selectRaw('a as output, lines.target_a as target');
        })

        // performance %
        ->when($dataset === 'p', function ($q) {
            return $q->selectRaw('p as output, lines.target_p as target');
        })

        // quality %
        ->when($dataset === 'q', function ($q) {
            return $q->selectRaw('q as output, lines.target_q as target');
        })

        // oee %
        ->when($dataset === 'oee', function ($q) {
            return $q->selectRaw('oee as output, lines.target_oee as target');
        })

        // filter by line if provided
        ->when($line_id !== null, function ($q) use ($line_id) {
            return $q->where('line_id', $line_id);
        })

        ->addSelect('start', 'lines.name as line_name')
        ->groupBy('line_id', 'start')
        ->orderBy('line_name', 'asc')
        ->orderBy('start', 'desc')
        ->get()

        // map output array
        ->mapToGroups(function ($item, $key) {
            return [
                $item->line_name => [
                    'target' => $item->target,
                    'output' => $item->output
                ]
            ];
        })->map(function ($item, $key) {
            return [
                'line_name' => $key,
                'data' => $item->take(36),
            ];
        });

        // get site totals
        $i = 0;
        $totals = [];
        foreach ($line_data as $line) {
            $output_sum = 0;
            foreach ($line['data'] as $data) {
                $output_sum += $data['output'];
            }
            $totals[$line['line_name']] = ['output' => $output_sum];
        }

        $summary = array();```

        // loop through each index
        for ($i = 0; $i < 36; $i++) {
            $totalOutput = 0;
            foreach($line_data as $line) {
                $totalOutput += $line['data'][$i]['output'];
            }

            // add total output to summary array
            array_push($summary, $totalOutput);
        }

        // divide and round percentages
        if(in_array($dataset, $percentages))
        {
            foreach($totals as $line => &$datum)
            {
                $datum['output'] = round($datum['output'] / count($line_data[$line]), 2);
            }
        }

        $table_data[$dataset] = array();

        array_push($table_data[$dataset], $summary);
        array_push($table_data[$dataset], $line_data->toArray());
    }

    return $table_data;
}

[main stirrup]

Is that how you mean?

[leaden hull]

It seems like it really needs al the data, so no help there.
But there's wa too much looping going on here. 7x(lines x subdata)x2 + another random mapper + all the other metric gatherers

I would first try to consolidate the loops. If its possible to produce some of these counts while the other loops are happening you'd save yourself a butload of compuete time.

then once they're consolidated you can start looking into just turning it into querries rather than doing all this compute in php

sadly most of this function is pretty vague, stuff like line and data are not exactly varnames you want.
Quite hard to make sense of any of it 😄

[main stirrup]

Someone said before that it would be possible to remove the select loop, but I do not really understand.