关于 sql 关联查询 解决方案 已解决

1. 问题描述

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

1. 我的解决方案如下两种（未解决）

1). 先查询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;
       });

2). 然后尝试第二种写法，通过时间筛选了，这样写又不符合效果图预期，假入今日没有新增数据，就查为空了，代码如下

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

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

3.解决方案如下 根据大佬指示，自己写了一遍，如下

当前使用的是tp框架

//模型
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 ) ) AND `fa_goods_log`.`deletetime` IS NULL LIMIT 1) AS `goods_count` FROM `fa_goods` ORDER BY `goods_count` DESC,`id` DESC LIMIT 0,5

剩下的就循环一下处理对比率，
再次感谢