如何优化重复查询？

Question

我必须使用输入的日期和结束日期进行查询。在这里，我在查询中多次重复相同的代码。在下面的示例中，只有第一个db_A & db_b获得的where条件与开始和结束值不同。

对于db_C，我没有"where('a.compid'，'9')“条件和开始/结束日期不同。

有人能帮助我如何优化这段代码吗？

这是我尝试过的代码。

$start = date("Y-m-01", strtotime($inputdate));
$end = $inputdate;

$db_A = DB::table('standard as a')
            ->join(DB::raw("(select distinct salid, cuscod, compid from stdmap_cus) b"), function($join){
                $join->on('a.compid', '=', 'b.compid')->on('a.cuscod', '=', 'b.cuscod');
            })
            ->select('b.salid', DB::raw('sum(turnover) as sumturn'))
            ->where('a.compid', '99999999')
            ->whereBetween('date', [$start, $end])
            ->groupBy('b.salid')
            ->get()->toArray();

$res_A = $this->getSalTurn(json_decode(json_encode($db_A), true));

$db_B = DB::table('standard as a')
            ->join(DB::raw("(select distinct salid, cuscod, compid from stdmap_cus) b"), function($join){
            $join->on('a.compid', '=', 'b.compid')->on('a.cuscod', '=', 'b.cuscod');
            })
            ->select('b.salid', DB::raw('sum(turnover) as sumturn'))
            ->where('a.compid', '!=', '99999999')
            ->whereBetween('date', [$startMTD, $endMTD])
            ->groupBy('b.salid')
            ->get()->toArray();

$res_B = $this->getSalTurn(json_decode(json_encode($db_B), true));

$start = date("Y-m-01", strtotime("-1 year", strtotime($inputdate)));
$end = date("Y-m-t", strtotime("-1 year", strtotime($inputdate)));


$db_C = DB::table('standard as a')
            ->join(DB::raw("(select distinct salid, cuscod, compid from stdmap_cus) b"), function($join){
                $join->on('a.compid', '=', 'b.compid')->on('a.cuscod', '=', 'b.cuscod');
            })
            ->select('b.salid', DB::raw('sum(turnover) as sumturn'))
            ->whereBetween('date', [$startMLY, $endMLY])
            ->groupBy('b.salid')
            ->get()->toArray();
$res_C = $this->getSalTurn(json_decode(json_encode($db_C), true));

Answer 1

您可以设置默认查询，然后克隆它。

$start = '...';
$end = '...';

$startMTD = '...';
$endMTD = '...';

$startMLY = '...';
$endMLY = '...';

$defaultQuery = DB::table('standard as a')
      ->join(DB::raw("(select distinct salid, cuscod, compid from stdmap_cus) b"), function($join){
          $join->on('a.compid', '=', 'b.compid')->on('a.cuscod', '=', 'b.cuscod');
      })
      ->select('b.salid', DB::raw('sum(turnover) as sumturn'))
      ->groupBy('b.salid');

$db_A = (clone $defaultQuery)
      ->where('a.compid', '99999999')
      ->whereBetween('date', [$start, $end])
      ->get();

$db_B = (clone $defaultQuery)
      ->where('a.compid', '99999999')
      ->whereBetween('date', [$startMTD, $endMTD])
      ->get();

$db_C = (clone $defaultQuery)
      ->whereBetween('date', [$startMLY, $endMLY])
      ->get();

$res_A = $this->getSalTurn($db_A->toArray();
$res_B = $this->getSalTurn($db_B->toArray();
$res_C = $this->getSalTurn($db_C->toArray());

我认为你不需要使用json_decode(json_encode($db_A), true)，它会返回你已经拥有的数组。