讨论数量:
代码如下,大概就是品牌表和品牌区域表,一个品牌对应多个品牌区域,一对多的关系,现在要求统计出来品牌的区域门店数,然后根据品牌的门店数进行排序分页,目前测试阶段数据是固定的,遇到的问题是在某一页的第一个数据和上一页的最后一个数据重复,猜想可能是在这个品牌的门店数和其他品牌的门店数相同导致的,但不知道怎么处理
$sql = DB::table('brands')->leftJoin('brands_scores',function($q)use($data_version){
return $q->on('brands_scores.brand_id','=','brands.id')->where('version',$data_version);
})
->leftJoin('brands_areas',function ($q){
return $q->on('brands_areas.brand_id','=','brands.id');
})
->selectRaw('hc_bang_brands.id,hc_bang_brands.name,hc_bang_brands.logo,hc_bang_brands.logo_display,hc_bang_brands_scores.score,
hc_bang_brands_scores.evaluate_score,hc_bang_brands_scores.avg_price,sum(hc_bang_brands_areas.num) as store_count');
if($keyword){
$brands_key = DB::table('brand_alias_ids')->where('brand_name_alias','like','%'.$keyword.'%')->selectRaw('DISTINCT(brand_id)')->pluck('brand_id')->toArray();
$sql = $sql->whereIn('brands.id',$brands_key);
}
if($master_type_id && !$second_type_id){
$sql = $sql->whereExists(function($q) use($master_type_id){
return $q->select(DB::raw(1))->from('brands_contact_type')->whereColumn('brands_contact_type.brand_id','brands.id')->where('master_type_id',$master_type_id);
});
}
if($second_type_id){
$sql = $sql->whereExists(function($q) use($second_type_id){
return $q->select(DB::raw(1))->from('brands_contact_type')->whereColumn('brands_contact_type.brand_id','brands.id')->where('second_type_id',$second_type_id);
});
}
if($city_id){
$sql = $sql->where('area_id',$city_id);
// $data = $data->whereExists(function($q) use($city_id){
// return $q->select(DB::raw(1))->from('brands_areas')->whereColumn('brands_areas.brand_id','brands.id')->where('area_id',$city_id);
// });
}
if($province_id && !$city_id){
$sql = $sql->where('area_pid',$province_id);
// $data = $data->whereExists(function($q) use($province_id){
// return $q->select(DB::raw(1))->from('brands_areas')->whereColumn('brands_areas.brand_id','brands.id')->where('area_pid',$province_id);
// });
}
if($order_by == 'score'){
$order_by = DB::raw('score*1');
}
$sql = $sql->groupBy('brands.id')->orderBy($order_by,$desc);
$data = DB::table(DB::raw("({$sql->toSql()}) as a"))
->mergeBindings($sql)
// ->paginate($pagesize,'*','page',$page);
->limit($pagesize)
->offset(($page - 1) * $pagesize)
->get();
$data = objectToArray($data);
$data = $this->deal_items($data);
$count = DB::table(DB::raw("({$sql->toSql()}) as a"))
->mergeBindings($sql)
->count();
$returnData = [
'data' => $data,
'total' => $count,
'last_page' => ceil($count/$pagesize)
];
return $returnData;
推荐文章: