mysql - Laravel 按日期分组模型，有 1 个查询

Question

我想知道是否可以通过 1 个查询按日期对约会进行分组，所以我的回复将是这样的（在 JSON 中）：

{  
   "appointmentsByDay":[  
      "01-07-2018":[  
         {  
            "appointment":"test"
         },
         {  
            "appointment":"test"
         }
      ],
      "02-07-2018":[  
         {  
            "appointment":"test"
         },
         {  
            "appointment":"test"
         }
      ],
      ..................
      "31-07-2018":[  
         {  
            "appointment":"test"
         },
         {  
            "appointment":"test"
         }
      ]
   ]
}

我知道这可能会在一个月内的所有日子里循环，但是我必须在一个月内调用类似的查询多达 31 次。

目前，我正在按以下方式在一个月之间完成所有约会，但这些约会并未按每天分组，并且介于更大范围之间的约会将被错误排序：

$startOfMonth = Carbon::parse($date)->startOfMonth();
$endOfMonth = Carbon::parse($date)->endOfMonth();

$appointments = Appointment::
    where('user_id', '=', $userId)->
    where(function ($query) use($endOfMonth, $startOfMonth) {
        $query->where(function ($query) use($endOfMonth, $startOfMonth) {
            $query->where('from_date', '<', $startOfMonth->format('Ymd'))
                  ->where('to_date', '>', $endOfMonth->format('Ymd'));
        })
        ->orWhere(function ($query) use($endOfMonth, $startOfMonth) {
            $query->where('from_date', '>=', $startOfMonth->format('Ymd'))
                  ->where('to_date', '<=', $endOfMonth->format('Ymd'));
        })
        ->orWhere(function ($query) use($endOfMonth, $startOfMonth) {
            $query->where('from_date', '<', $startOfMonth->format('Ymd'))
                  ->where('to_date', '>=', $startOfMonth->format('Ymd'));
        })
         ->orWhere(function ($query) use($endOfMonth, $startOfMonth) {
             $query->where('from_date', '<=', $endOfMonth->format('Ymd'))
                  ->where('to_date', '>', $endOfMonth->format('Ymd'));
        });
    })
    ->with(user')
    ->get();

我的模型如下所示：

namespace App\Models;

use Reliese\Database\Eloquent\Model as Eloquent;

class Appointment extends Eloquent
{
    protected $table = 'appointments';
    public $timestamps = false;

    protected $fillable = [
        'from_date',
        'to_date',
        'title'
    ];
}

我的约会表如下所示：（我使用的是 MYSQL）

+-----------+----------------+---------------------+-------------------+
| id (int)  | title (varchar)| from_date (varchar) | to_date (varchar) |
+-----------+----------------+---------------------+-------------------+
| 1         | appointment1   | 20180725            | 20180725          |
+-----------+----------------+---------------------+-------------------+
| 2         | appointment2   | 20180726            | 20180726          |
+-----------+----------------+---------------------+-------------------+
| 3         | appointment3   | 20180723            | 20180812          |
+-----------+----------------+---------------------+-------------------+
| 4         | appointment4   | 20180726            | 20180726          |
+-----------+----------------+---------------------+-------------------+
| 5         | appointment5   | 20180612            | 20181123          |
+-----------+----------------+---------------------+-------------------+

标签： mysqllaravelphp-7.2