Jump to content

COALESCE not working in CodeIgniter


JJM50
 Share

Go to solution Solved by kicken,

Recommended Posts

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:

image.thumb.png.80522c04a64effbca7b684bd475e2cfb.png

Edited by JJM50
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by JJM50
Link to comment
Share on other sites

  • Solution

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');

 

Link to comment
Share on other sites

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.

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.