上一篇:神经质的告白(2019-12-03 23:21:40)
文章大纲

laravel 中用group by和order by排序不正确的问题解决

2019-12-04 23:21:34

最近做数据统计,类如数据库里明明有11月30号的数据,我也明明按时间降序排序了,可是最终获取的数据时间,却不是11月30号这个最大时间,而是其它时间。


最终找出的问题原因就是:

mysql中order by是在group by之后执行的,而group by则是在同一组数据里挑选出id最小的数据记录作为汇总记录

可能有点模糊,下面拿我的博客举例子。

在实践之前,我先贴出我的服务器mysql版本:

mysql  Ver 15.1 Distrib 5.5.56-MariaDB


假如我要查询我的博客每个分类下最新的文章,我写了如下Sql语句:

select count(*) as count,news_id,title, cate_id, from_unixtime(create_time) from news n group by n.cate_id order by n.create_time desc;

查询的结果如下图:



图中高光的是我博客的php分类,但php分类下我最近一直在更新文章,所以php分类下最新的文章发布时间绝对不会是2017年。


正如上面原因提到的,group by会先于order by从我的博客每个分类下的文章组里挑出当组id最小的数据来展示。


所以网上常有group by和order by连用后数据排序不对的问题。


我把上面sql语句,改写成如下,即在子查询里先用order by将数据按时间降序排号,然后再group by:

SELECT count(*) as count, tmp.* FROM (SELECT news_id,title, cate_id, from_unixtime(create_time) FROM news ORDER BY create_time DESC) tmp GROUP BY tmp.cate_id


我以为会成功,事实并没有,此次执行结果如下图所示:



由图中高光时间可知,并未成功。


经过了解, 上面mysql子查询里order by之后必须要跟上limit,否则mysql优化会将上面mysql语句里的order by给优化掉。

改成如下,执行结果就ok了。看图中数据的时间部分就可辨别。

SELECT count(*) as count, tmp.* FROM (SELECT news_id,title, cate_id, from_unixtime(create_time) FROM news ORDER BY create_time DESC LIMIT 1000) tmp GROUP BY tmp.cate_id


执行结果:



本文是翟码农个人博客蓝翟红尘里php分类下的文章,转载请注明出处:http://www.zhai14.com/blog/2ade3f69c8410f534658e5a1d98b0ea3.html


好了,上面是Mysql语句解决order by和group by混用排序失效的方法。


那么在Laravel框架中,该怎么实现上述mysql语句的效果呢。

废话不多说了,直接上代码吧:

$query = News::where('created_at', '>', $startTime)
			->where('created_at', '<', $endTime)
			->where('status', 1)
			->whereIn('cate_id', [1,2,3,4])
			->select(DB::raw(" news_id,title, cate_id, from_unixtime(create_time) "));

$binding = $query->getBindings();  //获取where条件数据
$sqlStr  = $query->toSql();

//上面sqlStr打印出来,其中条件参数都是问号?,现在我们要将其替换成where条件的具体值
$afterSql = str_replace('?', "'?'", $sqlStr);
$realSql  = vsprintf(str_replace('?', '%s', $afterSql), $binding );


$sortResult = DB::table(DB::raw("({$realSql}) as tmp"))
		    ->groupBy('tmp.cate_id')
		    ->get();


上面代码里DB::table后面调用get方法得到的数据结构形式如下:

[ Object(StdClass), Object(StdClass)] 


foreach遍历上述结果$sortResult,每个Item都是对象,导致不能数组方式获取数据。

如果要用数组方式获取数据,就需要先将数组处理下。

数组不大时,即不考虑什么变量空间,采取如下解决方案即可:

$newSortResult = array_map(function($item){ 
	return $item->toArray();
}, $sortResult);



上一篇:神经质的告白(2019-12-03 23:21:40)
我要评论
评论列表