Блог

Главная - Вопросы по программированию - Рефакторинг обработки запроса Mysql на стороне PHP

Рефакторинг обработки запроса Mysql на стороне PHP

#php #mysql #codeigniter

Вопрос:

У меня есть запрос, который я использую в своей модели CodeIgniter для получения количества списков товаров между определенными днями. Что делает этот запрос, так это берет status_from из журналов, где дата находится во время и после выбранного диапазона дат, и берет added_date из списков, где дата выпадает перед диапазоном дат, выбранным пользователем, и вычисляет его. Как только он извлек эти записи, он проверяет таблицу на предмет того, какую переменную содержит этот статус, и выполняет a sum(case when else 0) , чтобы получить общее количество.

 function get_report(){
 $sql = with Y as (
  with recursive D (n, day) as (
    select 1 as n, '2021-09-25' my_date
    union
    select n 1, day   interval 1 day from D
      where day   interval 1 day < '2021-10-15'
  ) select * from D
), X as (
  select Y.day,
         l.*,
         (select status_from from logs
            where logs.refno = l.refno
              and logs.logtime >= Y.day
            order by logs.logtime
            limit 1) logstat
    from listings l, Y
    where l.added_date <= Y.day
), Z as (
  select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt
    from X
    group by X.day, stat_day
)
select Z.day,
  sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft,
  sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action,
  sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish,
  sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold,
  sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let
  from Z
  group by Z.day
  order by Z.day;
    $query = $this->db->query($sql);
    return $query;
}

Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0amp;fiddle=6789f0517b194b09d235860dc794ea14

Теперь здесь, как вы можете видеть, я обрабатываю свои вычисления в самой инструкции sql, но я хочу выполнить эти вычисления на своей стороне php, в основном повторяя каждый раз, когда он встречается Z.stat_day = 'D' , затем добавляя 1 в столбец черновика и то же самое для других статусов.

Текущий Класс Представления:

 <?php
            foreach($data_total as $row ){
               $draft = $row->draft ? $row->draft : 0;
               $publish = $row->publish ? $row->publish : 0;
               $action = $row->action ? $row->action : 0;
               $sold = $row->sold ? $row->sold : 0;
               $let = $row->let ? $row->let : 0;                              
          ?>
              <tr>
                    <td><?= $row->day?></td>
                    <td><?= $draft ?></td>
                    <td><?= $publish ?></td>
                    <td><?= $action ?></td>
                    <td><?= $sold ?></td>
                    <td><?= $let ?></td>
              </tr>
          <?php }  ?>

В принципе, я хочу преломить свой PHP-код, чтобы сделать то же самое, что делает оператор Mysql, но сохранить простой запрос и всю обработку на стороне PHP по соображениям производительности.

Ответ №1:

Начните с предварительного создания постоянной таблицы со списком «дней», уходящих далеко в будущее. Проиндексируйте его на день. Затем используйте эту таблицу вместо Y .

Вместо

 from listings l, Y
where l.added_date <= Y.day

вы, наверное, хотите

 FROM Y
LEFT JOIN listings AS l  ON l.added date = Y.day

WHERE Y.day BETWEEN ... AND ...

Таким образом, дни, которые отсутствуют, listings будут показаны в выходных данных. Однако значения будут отображаться как NULL , поэтому вы можете захотеть что-то вроде COALESCE(col, 0) вместо NULL .

Вы пытаетесь получить «совокупные» промежуточные итоги? (cf l.added_date <= Y.day ) Если это так, вам нужно взглянуть на «оконные» функции 8.0; они, вероятно, будут намного быстрее. (Вы используете 8.0?)

Метки: