关于 sql 关联查询 解决方案

问题描述

有两个表 goods 表内字段 id，name，type，等等其他字段 一对多关系
表二 goods_log 表字段 id，goods_id，createtime等其他字段
goods_id可以重复，
现在需要关联两个表，需求如下，通过筛选时间，今日，本周，本月，本年来排序
效果图如下

为此你搜索到了哪些方案及不适用的原因

我的解决方案
先查询goods表数据 ，再关联表goods_id ,这个的话，我只能再循环一次数据来排序，代码如下

$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;
                });

这样好像很麻烦，然后我再尝试写，通过时间筛选了，这样写又不符合效果图预期，假入今日没有新增数据，就查为空了，代码如下

$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();

请教大佬们有没有更优的解决方案，本人数据库子查询方面还欠缺，虚心请教。

解决办法 更新于2023-4-22

通过模型一对多关联

//模型
public function goods()
{
    return $this->hasMany('goods_log');
}

//控制器
$limit = 5; //每页显示条数
$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(); //返回总数
$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