COALESCE not working in CodeIgniter

[JJM50]

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=d0e8222a1e49774bcfbcfa30cec75732

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>

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:

Edited October 17, 2021 by JJM50

[Barand]

Can you you provide some test data for those two tables that exhibits this problem?

[JJM50]

3 minutes ago, Barand said:

Can you you provide some test data for those two tables that exhibits this problem?

So I have provided a dbfiddle with the data inside, and the dbfiddle provides me the right output that I want, but for some reason I cant get the statement correct in my model.

[Barand]

That dbfidlle link is as much use as a chocolate teapot.

[JJM50]

5 minutes ago, Barand said:

That dbfidlle link is as much use as a chocolate teapot.

Sorry I dont understand what you want. This is the data that is there in my database. If you want my query then I have done $this->db->last_query and got the following query:

SELECT SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft, S
UM(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")

which gave the following output as shown in my picture above

[Barand]

18 minutes ago, Barand said:

Can you you provide some test data for those two tables that exhibits this problem?

I'm sorry you find that request difficult to comprehend.

If I can see the data I might be able to work out why you are getiing the problem with that query.

Without the data I will have to create the tables myself, but as I don't yet know the cause, I cant add data that is going to cause the problem.

But as you are unwilling to help me to you with your problem, I'll bow out now. Good luck.

[JJM50]

8 minutes ago, Barand said:

I'm sorry you find that request difficult to comprehend.

If I can see the data I might be able to work out why you are getiing the problem with that query.

Without the data I will have to create the tables myself, but as I don't yet know the cause, I cant add data that is going to cause the problem.

But as you are unwilling to help me to you with your problem, I'll bow out now. Good luck.

How are you not able to get the data, it is all literally right there in the fiddle I have given. Also I don't understand how you came to the conclusion that I am unwilling to help you right now, when I'm trying to get what you want from me. Phrasing things like `That dbfidlle link is as much use as a chocolate teapot` doesn't really help me out as to what is wrong with what I have given to you already

Edited October 17, 2021 by JJM50

[kicken]

You need your join on the second table to be a LEFT JOIN, but right now you're just making it a regular (INNER) JOIN.  An inner join excludes the row from the result if no match is found in the second table.  A left join will include the row, but with all the fields from the second table set to NULL.

I don't know code igniter, but a simple search for code igniter left join suggests you want to write your join as:

$this->db->join('crm_clients_users c','t.agent_id = c.id', 'left');

 

[Barand]

3 hours ago, JJM50 said:

How are you not able to get the data, it is all literally right there in the fiddle I have given.

The dbfiddle link that was in your post when I tried it had two sets of "..." in the middle of the reference.

Quote

Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fid...30cec75732

The one that's there now is not the original.