我有一个模型,Article
article表数据如下,每一行对应一段数据
id = 1 , topic_id = 1 ,created_at =2016-05-28 12:22:33
id = 2 , topic_id = 1 ,created_at =2016-05-28 12:23:03
id = 3 , topic_id = 2,created_at =2016-05-28 12:23:33
id = 4 , topic_id = 3 ,created_at =2016-05-28 12:24:33
id = 5 , topic_id = 1 ,created_at =2016-05-28 13:22:33
现在想通过Article模型获取数据表里的所有数据,要求必须没有重复且是最新的
于是执行
Article::orderBy('topic_id','desc')->groupBy('topic_id')->get()
执行完成后 获取到的数据如下(id顺序请忽略)
id = 1 , topic_id = 1 ,created_at =2016-05-28 12:22:33
id = 3 , topic_id = 2,created_at =2016-05-28 12:23:33
id = 4 , topic_id = 3 ,created_at =2016-05-28 12:24:33
数据不是我想要,正确数据应该是
id = 5 , topic_id = 1 ,created_at =2016-05-28 13:22:33
id = 3 , topic_id = 2,created_at =2016-05-28 12:23:33
id = 4 , topic_id = 3 ,created_at =2016-05-28 12:24:33
结果和我想要的不同,原因可能是sql默认先执行了group by 而后再执行的order by
那么,遇到这种情况,在不写原生sql的情况下如何解决呢?
解决方法如下:
Article::with('content')
->whereIn('id',function($query){
$query->select(DB::raw('max(id)'))
->from('message')
->where('receive_id',5)
->where('type',4)
->orWhere('receive_id',0)
->groupBy('topic_id');
})
->paginate($page);