如何使用Laravel按日期对记录进行分组和计数? [英] How to group and count record by date using Laravel?

问题描述

我有一个问题表，它有一个列 created_at 和状态，可以为打开"或关闭"

我需要根据每月的状态来获取问题的数量.我想得到这样的东西:

  2018年1月:{打开:300，闭馆:28，}，2018年2月:{打开:250，封闭:80} 

我不知道是否有更好的方法，我对如何检索数据持开放式建议.基本上，我正在创建一个管理仪表盘，并希望在图表中显示数据.

解决方案

假设您使用的是MySQL:

  $ months = Issue :: select(DB :: raw("date_format(created_at，'％M％Y')month")，DB :: raw("sum(if(status ='Open'，1，0))打开")，DB :: raw("sum(if(status ='Closed'，1，0))已关闭"))-> groupBy('month')-> get(); 

您可以使用 keyBy()设置密钥:

  $ months = $ months-> keyBy('month'); 

I have an issues table, it has a column created_at, and status which can be 'Open' or 'Closed'

I need to get the count of issues depending on the status per month. I want to get something like this:

January 2018: {
  open: 300,
  closed: 28,
},
February 2018: {
  open: 250,
  closed: 80
}

I don't know if there's a better approach, I'm open to suggestion in how I would retrieve the data. Basically I'm creating an admin dashboard and want to display the data in a chart.

解决方案

Assuming you are using MySQL:

$months = Issue::select(
    DB::raw("date_format(created_at, '%M %Y') month"),
    DB::raw("sum(if(status = 'Open', 1, 0)) open"),
    DB::raw("sum(if(status = 'Closed', 1, 0)) closed")
)->groupBy('month')->get();

You can set the key with keyBy():

$months = $months->keyBy('month');

这篇关于如何使用Laravel按日期对记录进行分组和计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！