[SOLVED] "Unknown column in where clause" error when CodeIgniter active record query references a SELECT column alias in a WHERE condition

"Unknown column in where clause" error when CodeIgniter active record query references a SELECT column alias in a WHERE condition

I am trying a CONCAT() call for an autocomplete, using CodeIgniter's Active Record.

My Query is:

$this->db->select("CONCAT(user_firstname, '.', user_surname) AS name", FALSE);
$this->db->select('user_id, user_telephone, user_email');
$this->db->from('users');
$this->db->where('name', $term);

If $term = 'foo', then my rendered SQL would be:

SELECT CONCAT(user_firstname, '.', user_surname) AS name, `user_id`, `user_telephone`, `user_email`
FROM `users`
WHERE `name` = 'foo'

My problem is reproducible in this SQLize sandbox demonstration.

I keep getting an MySQL Error from this saying:

Error Number: 1054
Unknown column 'name' in 'where clause'

Which is true, however I have just created in my CONCAT() statement in the SELECT clause. I ideally need $term to match the concatenated firstname and surname fields.

Any ideas what I can do to improve this? I am considering just writing this as a flat MySQL query.

Solution

$this->db->select('user_id, user_telephone, user_email, CONCAT(user_firstname, '.', user_surname) AS name', FALSE);
$this->db->from('users');
$this->db->where('name', $term);

Not sure why you are running multiple selects. So just put it as a single select.

The above script will render the following SQL (depending on db driver / dialect).

SELECT user_id, user_telephone, user_email, CONCAT(user_firstname, user_surname) AS name
FROM `users`
WHERE `name` = 'foo'