#php #laravel #query-builder
Вопрос:
Я использую конструктор запросов в своем проекте Laravel 8 для создания ежемесячной суммы всех удаленных пользователей в моем приложении, затем я вывожу два элемента для использования в качестве части графика, total
и date
.
Это хорошо работает, но если бы в течение месяца не было никаких данных, то они бы сразу перешли на следующий месяц, например:
- 2021-01
- 2021-04
- 2021-05
Как я могу изменить запрос, чтобы добавить все месяцы, начиная с заданной даты начала, до «сейчас», и эффективно добавить пустые значения для тех месяцев, в которых нет данных?
Мой текущий запрос таков:
$data = User::selectRaw('DATE_FORMAT(created_at, "%Y-%m") as date, COUNT(*) as total') -gt;groupByRaw('DATE_FORMAT(created_at, "%Y-%m")') -gt;withTrashed() -gt;whereNotNull('deleted_at') -gt;get();
И я подумываю о том, чтобы рассчитать начало, сделав что-то вроде этого:
$user = User::orderBy('created_at', 'asc')-gt;first(); $start = $user-gt;created_at; $data = User::selectRaw('DATE_FORMAT(created_at, "%Y-%m") as date, COUNT(*) as total') -gt;groupByRaw('DATE_FORMAT(created_at, "%Y-%m")') -gt;withTrashed() -gt;whereNotNull('deleted_at') -gt;get(); $end = Carbon::now()-gt;endOfMonth();
Не знаю, как вставить это в запрос, хотя
Ответ №1:
Проблема здесь в том, что группы возникают из строк, а не наоборот. Группа не будет существовать, если не существует строки, которая должна быть включена в группу. Вы видите «отсутствующие» месяцы только потому, что, по вашему мнению, между январем и апрелем есть месяцы.
Я бы рекомендовал делать это в процессе постобработки, потому что любые разумные попытки создать фантомные строки, чтобы появлялись группы, неизбежно будут более сложными и более сложными в обслуживании.
Это может показаться неуклюжим, но перебор месяцев из углерода и добавление значений в результат вашего запроса будет работать нормально. Кроме того, вам не нужно полагаться на $start с вашего первого пользовательского результата, вы можете установить его самостоятельно.
$start = Carbon::today()-gt;subYear(); // Use any start date, even include it from user input (like a datepicker). $end = Carbon::today(); // Use any end date, though it won't be useful any later than today. $loop = $start-gt;copy(); while ($loop-gt;lessThanOrEqualTo($end)) { $exists = $data-gt;first(function($item) use ($end) { return $item-gt;date == $end-gt;format('Y-m'); }); if (!$exists) { $row = new stdClass(); $row-gt;date = $loop-gt;copy()-gt;format('Y-m'); $row-gt;total = 0; $data-gt;push($row); } $loop-gt;addMonth(); // This keeps the loop going. }
Это выполняет то, что вы хотите, и не затрагивает никаких проблем N 1.
Изменить: Добавлен пример ниже в многоразовой функции.
function fillEmptyMonths(Collection $data, Carbon $start, Carbon $end): Collection { $loop = $start-gt;copy(); // Loop using diff in months rather than running comparison over and over. for ($months = 0; $months lt;= $start-gt;diffInMonths($end); $months ) { if ($data-gt;where('date', '=', $loop-gt;format('Y-m'))-gt;isEmpty()) { $row = new stdClass(); $row-gt;date = $loop-gt;copy()-gt;format('Y-m'); $row-gt;total = 0; $data-gt;push($row); } $loop-gt;addMonth(); } return $data; }
Вы также можете расширить это, чтобы взять другой параметр, определяющий приращение (и передать его «месяц», «день», «год» и т. Д.). Но если вы используете только месяц, это должно сработать.
Комментарии:
1. Это выглядит хорошо, но обычно
while
циклы не поощряются, и в моем контексте я использовал только один запрос, в моем приложении у меня есть 4, назначенные разным переменным, и поэтому я не хотел бы дублировать приведенный выше код 4 раза. Есть ли способ, которым вы могли бы сжать это и извлечь в общую функцию, которая принимает начало, конец и данные и возвращает данные с отсутствующими месяцами?2. @RyanH Я отредактировал свой комментарий выше с помощью такой функции, как образец.