CodeIgniter query builder: Unknown column when WHERE uses a SELECT alias
Unknown column 'cnt' in 'where clause'Short answer
The error comes from SQL, not from CodeIgniter: a WHERE clause cannot refer to an alias created in the select list, because WHERE is evaluated before that value exists. Repeat the expression inside where(), or for an aggregate such as COUNT move the condition to having() together with groupBy().
Fix it fast
1Find out which cause you have
Print the SQL the builder generated (CodeIgniter 4) and see where the alias is used. On CodeIgniter 3, read the last query from the database error page instead.
echo $builder->getCompiledSelect();2Match what you see, then jump to its fix
- 1
Unknown column 'full_price' in 'where clause' and full_price is defined as price * qty AS full_priceA calculated column alias is used in where()Fix - 2
Unknown column 'cnt' in 'where clause' on a query with groupByAn aggregate alias such as COUNT(*) is used in where()Fix - 3
The generated SQL shows backticks around parts of an expression, or a broken aliasA subquery or function in select is mangled by escapingFix
- 1
What does the error look like?
Unknown column 'cnt' in 'where clause'
Cause 1 of 3
A calculated column alias is used in where()
1Check
Find the alias in the select list, then find the same word in where(). Standard SQL disallows aliases in WHERE, because the value is not known when WHERE runs.
echo $builder->getCompiledSelect();2Fix
Write the expression in the where() call. Pass false as the third argument so CodeIgniter does not try to quote the parts of the expression.
// CodeIgniter 4
$builder->select('id, price * qty AS full_price', false)
->where('price * qty >', 100, false);
// CodeIgniter 3
$this->db->select('id, price * qty AS full_price', FALSE);
$this->db->where('price * qty > 100', NULL, FALSE);3Verify
Compile the query again; the WHERE line now holds the expression, not the alias.
echo $builder->getCompiledSelect();Confirmed by dev.mysql.com
Cause 2 of 3
An aggregate alias such as COUNT(*) is used in where()
1Check
WHERE decides which rows are grouped, so it cannot use a value that only exists after grouping. MySQL allows the alias in HAVING, GROUP BY and ORDER BY.
echo $builder->getCompiledSelect();2Fix
Move the condition to having().
// CodeIgniter 4
$builder->select('user_id, COUNT(*) AS cnt')
->groupBy('user_id')
->having('cnt >', 5);3Verify
The generated SQL shows HAVING cnt > 5 after GROUP BY.
echo $builder->getCompiledSelect();Confirmed by dev.mysql.com
Cause 3 of 3
A subquery or function in select is mangled by escaping
1Check
CodeIgniter protects field and table names. A compound expression in select() can break under that protection, which is why the second argument exists.
echo $builder->getCompiledSelect();2Fix
Pass false as the second argument of select() so the expression goes out as written.
$builder->select('(SELECT SUM(payments.amount) FROM payments WHERE payments.invoice_id = invoices.id) AS amount_paid', false);3Verify
Compile the query again and read the select line.
echo $builder->getCompiledSelect();Confirmed by codeigniter.com
How do I stop it coming back?
- Remember the order SQL works in: WHERE filters rows, then GROUP BY, then HAVING, then the select list names appear.
- Print the compiled query while you build it, not after it fails.
- Escape values yourself when you pass a custom where string and turn escaping off.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Back end
Selecting rows where a column is not null in the CodeIgniter query builder
IS NOT NULL appears inside quotes in the compiled SQL
The query builder escapes values, so a value such as 'IS NOT NULL' becomes a quoted string, not SQL.
3 causes, each with a fixDatabases
A JOIN query fails with 'Column ... is ambiguous'
ERROR 1052 (23000): Column 'i' in on clause is ambiguous
Error 1052 means a column name exists in more than one table of the query and MySQL cannot tell which you mean.
3 causes, each with a fixBack end
CodeIgniter 4: try/catch does not catch a failed insert in the model
DatabaseException
CodeIgniter throws a DatabaseException on a failed query only when DBDebug is true, and inside a transaction it does not throw unless you ask.
3 causes, each with a fixBack end
CodeIgniter 4 where() builds a different condition than you meant
A SQL syntax error that appears only when a value contains a quote character
The query builder joins every where() with AND and every orWhere() with OR, with no brackets.
3 causes, each with a fixBack end
Call to undefined function mysql_pconnect() after a PHP upgrade
Call to undefined function mysql_connect()
The old mysql_* functions belonged to the ext/mysql extension, which PHP removed in version 7.0.
2 causes, each with a fixBack end
CodeIgniter: Call to undefined method on the result object when using order_by() after get_where()
Call to undefined method CI_DB_mysql_result::order_by()
get_where() runs the query at once and returns a result object, not the query builder.
3 causes, each with a fix