Jump to content

Refactoring the processing of Mysql query to PHP side


JJM50
 Share

Recommended Posts

I have a query I'm using in my CodeIgniter model to fetch the count of listings of products between particular days. What this query is doing is it's taking status_from from logs where the date is during and after the selected date range and taking added_date from listings where the date falls before the from date range picked by the user and calculates it. Once it has retrieved those records, it checks the table for what variable that status holds and does a sum(case when else 0) to get the total count.

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.0&fiddle=6789f0517b194b09d235860dc794ea14

Now here as you can see I'm processing my calculations in the sql statement itself, but I want to do these calculations in my php side, basically iterating everytime it encounters Z.stat_day = 'D' then adding 1 to Draft column and same for the other statuses.

Current View Class:

<?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 }  ?>

Basically I want to refractor my PHP code to do the same thing the Mysql statement is doing, but keeping the query simple and all processing in the PHP side for performance reasons.

Edited by JJM50
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.