更快的方式来填充数据mysql和php [英] Faster way to populate data mysql and php

问题描述

您好，我使用Laravel 5和Laravel Excel http://www.maatwebsite.nl/laravel -excel / docs 。

Hi I am using Laravel 5 and Laravel Excel http://www.maatwebsite.nl/laravel-excel/docs.

我在一个报告模块中工作，将导出为ex​​cel。我有三个表。

I am working in a reporting module that will be exported to excel. I have three tables.

表单，FormsResponses和指标

Forms
->id
->phone_number
->calldatetime

FormsReponses
->id
->Forms_id
->Metrics_id
->Response

Metrics
->id
->description

因此，您可以看到我有一个Form，其中包含指标列表，然后用户将为每个指标选择响应。重定向可以是Yes，No，N / A。我有这份报告。让我们说，现在有3个指标，如果我们添加新的指标，可以增加指标。

So as you can see I have a Form which has a list of metrics and then the user will select responses for each metrics. Resposes can be Yes,No,N/A. And I have this report. Let's say I have 3 Metrics for now which can be increased if we added new Metrics.

Metrics ID|Metrics Description|Phone1|Phone2|Phone3 ...
__________|___________________|______|______|______|___
1         |Sample1            |Yes   |Yes   |Yes 
__________|___________________|______|______|__________
2         |Sample2            |Yes   |No    |No
__________|___________________|______|______|__________
3         |Sample3            |Yes   |No    |No

所以我要做的是显示每个指标的每个电话号码的回复。我有一个从开始日期和到日期。此报告将在我已经有的excel文件中输出。因此，此报表会根据我在日期范围内获得的电话号码数量，垂直（取决于指标数量）以及水平展开。我可以正确地populte。但它太慢了。处理需要很多如果时间，我看到这是因为在我的代码中搜索响应。我的代码：

So what I'm doing is I am showing the Responses of each phone number on each metrics. I have a From Date and To Date. This report will be outputed in an excel file which I already have. So this report expands Vertically (Depends on the number of Metrics) and as well Horizontally depending on the number of phone numbers i will get in my Date Range. I can populte it properly. But it's too slow. Processing takes a lot if time and I see that it is cause in my code that searches for the Responses. My code:

$query = "SELECT id, phone FROM qcv.forms WHERE calldatetime >= '$from' AND calldatetime <= '$to' ORDER BY id ASC ;";
$phone = DB::connection('mysql')->select($query);

      $metrics = Metric::all();
      $metric_start = 10;
      $start = "D";
      $count = 10;

      foreach ($phone as $key => $value2) // Populate Phone Numbers Horizontally
      {
          $sheet->cell($start.'9', $value2->phone);
          // This will fill the responses for each number
          foreach ($metrics as $key => $value)
          {
              $responses = FormResponses::where('form_id', '=', $value2->id)->where('metrics_id', '=', $value->id)->get();
              $sheet->cell($start.$count, $responses[0]->response);
              // Populate Metrics Vertically
              $sheet->cell('C'.$count, $value->question);
              $sheet->cell('B'.$count, $value->description);
              $sheet->cell('A'.$count, $value->metrics_name);
             $count++;
          }
          $start++;
          $count = 10;
      }

和

$responses = FormResponses::where('form_id', '=', $value2->id)->where('metrics_id', '=', $value->id)->get();

该行占用时间，因为每个电话号码都必须搜索每个指标的响应有。所以想象一下，如果我有30个指标和150个电话号码。这将需要时间。是否有更好的方法？

That line takes up the time because for each phone number it has to search the responses for each metrics I have. So imagine if I have 30 Metrics and 150 Phone numbers. It will take time. Is there a better approach to it?

推荐答案

这不是一个更好的方法使用 fromArray 方法，它似乎更有效率创建跟踪它自己。

Isn't it a better approach to use fromArray method, it would seem it's more efficient instead of creating to track it yourself. I used to same strategy in a export script i created for our company.

创建您希望的数组，例如：

$forms = [
    ['id', 'phone_number', 'calldatetime'], // headers
    // total of 5 dataset items
];

$formsReponsesStartAtRow = $formsStartAtRow + count($forms) + ;
$formsReponses = [
    ['id', 'Forms_id', 'Metrics_id', 'Response'], // headers
    // total of 15 dataset items
];

$metrics = [
    ['id', 'description']
    // total of 100 dataset items
];

当您的数据准备就绪后，从每个数组计算开始行。

When your data is ready calculate the start row from each array.

// leave between each dataset 2 rows
$rowSpacing = 2;
$startCol = 'A';

// start cell A1
$startAtRow = 1;
$forms = [...];
// add $forms to the sheet
$sheet->fromArray($forms, null, $startCol . $startAtRow);

// calculate start row from previous $startAtRow
// start cell is: 5 + 2 = 7 = A7
$startAtRow = $startAtRow + count($forms) + $rowSpacing;
$formsReponses = [...];
// add $formsReponses to the sheet
$sheet->fromArray($formsReponses, null, $startCol . $startAtRow);

// calculate start row from previous $startAtRow
// start cell is: 7 + 15 + 2 = 24 = A24
$startAtRow = $startAtRow + count($formsReponses) + $rowSpacing;
$metrics = [...];
// add $metrics to the sheet
$sheet->fromArray($metrics, null, $startCol . $startAtRow);

如果添加另一个将开始的数据集： 24 + 100 + 2 = 126 = A126

If you add a another dataset that one will start on: 24 + 100 + 2 = 126 = A126

每当您的数据集增长时，您的excel都会预期变化。

Whenever your dataset grows your excel anticipate on the changes.

希望这有助于你。

这篇关于更快的方式来填充数据mysql和php的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！