有兩個(gè)表 goods 表內(nèi)字段 id,name,type,等等其他字段 一對(duì)多關(guān)系
表二 goods_log 表字段 id,goods_id,createtime等其他字段
goods_id可以重復(fù),
現(xiàn)在需要關(guān)聯(lián)兩個(gè)表,需求如下,通過篩選時(shí)間,今日,本周,本月,本年來排序
效果圖如下
我的解決方案
先查詢goods表數(shù)據(jù) ,再關(guān)聯(lián)表goods_id ,這個(gè)的話,我只能再循環(huán)一次數(shù)據(jù)來排序,代碼如下
$whereTime = $this->request->param('time')??'d';
switch ($whereTime) {
case 'w':
$whereTime = 'w';
break;
case 'm':
$whereTime = 'm';
break;
case 'y':
$whereTime = 'y';
break;
default:
$whereTime = 'd';
break;
}
$data = Db::name('goods')->field('id,name,type')
->paginate()
->each(function($item, $key) use ($whereTime) {
$item['click'] = Db::name('goods_log')->where('goods_id',$item['id'])->whereTime('createtime',$whereTime)->count();
$item['contrast'] = 5;
$item['symbol'] = $item['click']/$item['contrast'] > 0 ? 0 : 1 ;//0=降下 1=上升
return $item;
});
這樣好像很麻煩,然后我再嘗試寫,通過時(shí)間篩選了,這樣寫又不符合效果圖預(yù)期,假入今日沒有新增數(shù)據(jù),就查為空了,代碼如下
$list = Db::name('goods')->alias('g')
->field('g.id,g.name,count(l.goods_id) as click,l.createtime')
->join('goods_log l','g.id = l.goods_id','LEFT')
->whereTime('l.createtime',$whereTime)
->group('l.goods_id')
->order('click desc,g.id desc')
->select();
請(qǐng)教大佬們有沒有更優(yōu)的解決方案,本人數(shù)據(jù)庫子查詢方面還欠缺,虛心請(qǐng)教。
通過模型一對(duì)多關(guān)聯(lián)
//模型
public function goods()
{
return $this->hasMany('goods_log');
}
//控制器
$limit = 5; //每頁顯示條數(shù)
$page = 1; //第幾頁
$query = Goods::field('id,name,type')->withCount([
'goods' => function( $query ) use ( $whereTime ){
$query->whereTime('createtime',$whereTime);
}
]);
$data = $query->page($page,$limit)
->order('goods_count desc,id desc')
->select();
$total = $query->count(); //返回總數(shù)
$this->success('查詢成功',['total'=>$total,'page'=>$page,'limit'=>$limit,'data'=>$data]);
打印sql與截圖
SELECT `id`,`name`,`type`,(SELECT COUNT(*) AS tp_count FROM `fa_goods_log` WHERE ( `createtime` BETWEEN 1672502400 AND 1704038400 AND ( `goods_id` = fa_goods.id ) ) LIMIT 1) AS `goods_count` FROM `fa_goods` ORDER BY `goods_count` DESC,`id` DESC LIMIT 0,5