在Codeigniter ActiveRecord查询中使用IFNULL和CONCAT

Question

I'm using the Codeigniter ActiveRecord ORM to conduct a query that will select some data from my database. One of the bits of data in my database table is from the column called updated_by which is represented by an integer from the users table which tells which user updated that particular record. This integer is currently a int(11) that defaults to NULL when the initial record is created and can obviously have a value of NULL for the column.

When the query is ran I do not get a value for updated_by field. Is there something I'm doing wrong?

$this->db->select("IFNULL(CONCAT_WS(' ', updator.first_name, updator.last_name), '----') AS updated_by", FALSE);

For the comments made below this was my echoed query statement.

SELECT `pages`.`page_id`, `pages`.`page_name`, `pages`.`created_at`, `pages`.`updated_at`, `statuses`.`status_name`, CONCAT_WS(' ', creator.first_name, creator.last_name) AS created_by, IFNULL(CONCAT_WS(' ', updator.first_name, updator.last_name), '----') AS updated_by FROM (`pages`) JOIN `users` AS creator ON `creator`.`user_id` = `pages`.`created_by` LEFT JOIN `users` AS updator ON `updator`.`user_id` = `pages`.`updated_by` JOIN `statuses` ON `statuses`.`status_id` = `pages`.`status_id`

Answer 1

The problem basically is that CONCAT_WS() will never return NULL, you can run the query

SELECT CONCAT_WS(' ',NULL,NULL);

And it will always return a blankspace, therefore if there is not updater.firstname or updater.last_name it will return you a blankspace instead of the NULL value you're expecting to get.

You can do this by changing your IFNULL to a simple IF

IF(updator.firstname IS NULL, '----', CONCAT_WS(' ', updator.first_name, updator.last_name))