php - SQL:使用计数查询返回 1

Question

我有一个 PHP(MySQL) 查询。我正在从表中获取记录并将它们返回到数据表。

问题是当我在查询中使用 count() 时它总是返回 1。

但是，如果我计算数组的元素，那么结果就是预期的。它返回所有 8 条记录。

以下是我的代码:

public function job_order_detail_del($arr=array()) {
        $count = 0;
        $start = isset($arr['start'])?$arr['start']:0;
        $length = isset($arr['length'])?$arr['length']:0;
        $search = isset($arr['search'])?$arr['search']:'';
        $orderBy = isset($arr['orderBy'])?$arr['orderBy']:'';
        $orderDir = isset($arr['orderDir'])?$arr['orderDir']:'';
        $aufnr = isset($arr['aufnr'])?$arr['aufnr']:0;
        $aufpl = isset($arr['aufpl'])?$arr['aufpl']:0;
        $type = isset($arr['type'])?$arr['type']:'';
        $whr = array('h.stats' => '', 'h.bukrs' => Session::get('company'), 'h.delstats' => '');
        if ($aufnr > 0)
            $whr['a.aufnr'] = $aufnr;
        if ($aufpl > 0)
            $whr['a.aufpl'] = $aufpl;

        $a = DB::table('zpp_afpo_h as h')
                ->leftjoin('zpp_afpo as a ', function($join) {
                    $join->on('a.aufnr', '=', 'h.aufnr')
                    ->where('a.bukrs', '=', Session::get('company'))
                    ->where('a.stats', '=', '');
                })
                ->leftjoin('zpp_afvc as b ', function($join) {
                    $join->on('a.aufnr', '=', 'b.aufnr')
                    ->on('a.aufpl', '=', 'b.aufpl')
                    ->where('b.bukrs', '=', Session::get('company'))
                    ->where('b.stats', '=', '');
                })
                ->leftjoin('zpp_afru as c ', function($join) use($type) {
                    $join->on('c.aufnr', '=', 'b.aufnr')
                    ->on('c.rueck', '=', 'b.rueck');
                })
                ->where('c.type', '=', $type)
                    ->where('c.bukrs', '=', Session::get('company'))
                    ->where('c.stats', '=', '')
                    ->where('c.delstats', '=', '')
                ->select('h.idat2', 'a.matnr', 'b.arbpl', 'a.gamng as total', 'b.rueck', 'h.priority', 'b.vornr', 'b.prev_startper', 'b.scrap', 'h.dispatch_date', 'h.order_start_dt', 'h.requirement_dt', 'h.ktxt', 'c.rmzhl', 'c.budat', 'c.aufnr', 'c.rework_lmnga', DB::raw('sum(c.lmnga) as sum_cnfrm'));
        if ($aufnr == '') {
            $a->where('h.idat2', '=', '0000:00:00');
        }
        $a->where($whr)
                ->groupby('b.rueck')
                ->groupby('c.rmzhl')
                ->groupby('c.counter');
        if($orderBy!=''){
                $a->orderBy($orderBy,$orderDir);
        }
        if($search!=''){
          $dt_srch = implode('-',array_reverse(explode('-',$search)));
          $a->where(function($query) use ($search,$dt_srch){
            $query->where('c.aufnr','LIKE','%'.$search.'%')
                  ->orWhere('a.matnr','LIKE','%'.$search.'%')
                  ->orWhere('c.budat','LIKE','%'.$dt_srch.'%')
                  ->orWhere('b.arbpl','LIKE','%'.$search.'%')
                  ->orWhere('a.gamng','LIKE','%'.$search.'%');
          });
        }
        if($length>0){
          $get_rec = $a->skip($start)->take($length)->get();
        }else{
          $get_rec = $a->get();
          $count = count($get_rec);
//          $count = $a->count(); //Problem is here
          return $count;
        }
        $arr = array();
        foreach($get_rec as $l){
                    if($length>0 || Input::get('open') == 1 || Input::get('open') == 2){
                        $arr = DB::table('maras')
                                    ->where('matnr','=',$l->matnr)
                                    ->where('bukrs','=',Session::get('company'))
                                    ->where('stats','=','')
                                    ->where('delstats','=','')
                                    ->select(DB::raw('GROUP_CONCAT(distinct(matnr)) as mat'),DB::raw('GROUP_CONCAT(distinct(mdesc)) as mat_desc'))
                                    ->groupby('matnr')
                                    ->first();
                        $l->matnr = $arr->mat;
                        $l->mat_desc = $arr->mat_desc;
                    }

                }

        return $get_rec;
    }

请让我知道问题所在。提前致谢。

Answer 1

您正在 groupBy 元素上使用 count，并且 count 仅返回分组 计数。这就是为什么它只返回单行或1。在这种情况下，您可以计数返回的集合，例如-

$count = $get_rec->count();