Search code examples
phpmysqlcodeigniter

COALESCE not returning an extra row for Null and 0 values

Currently I have 2 tables, the first table shows a count of statuses, refno. and agent_id(person in charge of the refno.) and the second table has an id and agent_name. So to refer a particular agent next to the refno. in table 1, you can reference it via the id of the agent table.

Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8b92273ef2bb807e3a23e4b8a2ce6d6b

Now I have found out that some of my listings have the agent_id as 0 and null, which doesn't have a reference in my agents table. So here I'm using COALESCE to add an extra row called Unassigned and inserting all variables with agent_id 0 or null inside this column. I've tried this same in my codeigniter model:

function get_totalagentstatus(){
$this->db->select("SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft,
SUM(CASE WHEN t.status = 'N' THEN 1 END) AS unpublish,
SUM(CASE WHEN t.status = 'Y' THEN 1 END) AS publish,
SUM(CASE WHEN t.status = 'U' THEN 1 END) AS action,
SUM(CASE WHEN t.status = 'L' THEN 1 END) AS unlisted,
SUM(CASE WHEN t.status = 'S' THEN 1 END) AS sold,
SUM(CASE WHEN t.status = 'T' THEN 1 END) AS let, COALESCE(c.display_name,'Unassigned'),
SUM(t.status = 'D') +SUM(t.status = 'N') + SUM(t.status = 'Y') + SUM(t.status = 'U') +
        SUM(t.status = 'L' ) + SUM(t.status = 'S' )+ SUM(t.status = 'T' ) AS total, t.agent_id, c.display_name");
$this->db->from('crm_listings t');
$this->db->join('crm_clients_users c','t.agent_id = c.id');
$this->db->where('archive="N"');
$this->db->group_by('COALESCE(c.display_name,"Unassigned")');
$results = $this->db->get();
  return $results;
}

Controller Class:

$content['total_agent_status'] = $this->leads_model->get_totalagentstatus()->result();

View Class:

<?php
            foreach($total_agent_status as $row ){
              $draft = $row->draft ? $row->draft : 0;
              $unpublish = $row->unpublish ? $row->unpublish : 0;
              $publish = $row->publish ? $row->publish : 0;
              $action = $row->action ? $row->action : 0;
              $unlisted = $row->unlisted ? $row->unlisted : 0;
              $sold = $row->sold ? $row->sold : 0;
              $let = $row->let ? $row->let : 0; 
              $total = $row->total ? $row->total : 0;                           
          ?>
          <tr>
                <td><?= $row->display_name ?></td>
                <td><?= $draft ?></td>
                <td><?= $unpublish ?></td>
                <td><?= $publish ?></td>
                <td><?= $action ?></td>
                <td><?= $unlisted ?></td>
                <td><?= $sold ?></td>
                <td><?= $let ?></td>
                <td><?= $total ?></td>
          </tr>

I have done $this->db->last_query and got the following query:

SELECT SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft, 
SUM(CASE WHEN t.status = 'N' THEN 1 END) AS unpublish, 
SUM(CASE WHEN t.status = 'Y' THEN 1 END) AS publish, 
SUM(CASE WHEN t.status = 'U' THEN 1 END) AS action, 
SUM(CASE WHEN t.status = 'L' THEN 1 END) AS unlisted, 
SUM(CASE WHEN t.status = 'S' THEN 1 END) AS sold, 
SUM(CASE WHEN t.status = 'T' THEN 1 END) AS let, 
COALESCE(c.display_name, 'Unassigned'), SUM(t.status = 'D') 
+SUM(t.status = 'N') + SUM(t.status = 'Y') + SUM(t.status = 'U') 
+ SUM(t.status = 'L' ) + SUM(t.status = 'S' )+ SUM(t.status = 'T' ) AS total, 
`t`.`agent_id`, `c`.`display_name` FROM `crm_listings` `t` 
JOIN `crm_clients_users` `c` ON `t`.`agent_id` = `c`.`id` 
WHERE `archive` = "N" GROUP BY COALESCE(c.display_name, "Unassigned")

Now this returns everything except the Unassigned row which I want. I've also input this in my phpmyadmin to see the result and it does not return it there either, instead it shows the output with these headers and Unassigned is not there in any of the entries here:

enter image description here

Solution

  • You need a LEFT join of listings to agents if you want in the results the rows of listings that do not have a matching id in agents.
    Also, you must group by COALESCE(t.agent_id, 0) to cover both cases of 0 and null in agent_id:

    SELECT COALESCE(c.name, 'Unassigned') name,
       SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft, 
       SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish, 
       SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish, 
       SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action, 
       SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted, 
       SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold, 
       SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
       SUM(CASE WHEN t.status IN ('D', 'N', 'Y', 'U', 'L', 'S', 'T') THEN 1 ELSE 0 END) AS total 
FROM listings t LEFT JOIN agents c 
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;

    I added an ELSE 0 part in all CASE expressions so that you get 0s in the results instead of NULLs and changed the expression to just 1 SUM for the column total by using the operator IN, but if 'D', 'N', 'Y', 'U', 'L', 'S' and 'T' are the only possible values of status then instead you can just use COUNT(*):

    SELECT COALESCE(c.name, 'Unassigned') name,
       SUM(CASE WHEN t.status = 'D' THEN 1 ELSE 0 END) AS draft, 
       SUM(CASE WHEN t.status = 'N' THEN 1 ELSE 0 END) AS unpublish, 
       SUM(CASE WHEN t.status = 'Y' THEN 1 ELSE 0 END) AS publish, 
       SUM(CASE WHEN t.status = 'U' THEN 1 ELSE 0 END) AS action, 
       SUM(CASE WHEN t.status = 'L' THEN 1 ELSE 0 END) AS unlisted, 
       SUM(CASE WHEN t.status = 'S' THEN 1 ELSE 0 END) AS sold, 
       SUM(CASE WHEN t.status = 'T' THEN 1 ELSE 0 END) AS let,
       COUNT(*) AS total 
FROM listings t LEFT JOIN agents c 
ON t.agent_id = c.id
GROUP BY COALESCE(t.agent_id, 0), c.name
ORDER BY c.name IS NULL, c.name;

    See the demo.