CodeIgniter query builder script with two joined tables causes error: "Unknown column 'category' in 'where clause'"

Question

I am trying to inner join two tables using CodeIgniter's query builder. My coding attempt is emitting an error:

Unknown column 'category' in 'where clause'

My coding attempt:

$query = $this->db
    ->select('title, name as category, rental_rate, length')
    ->order_by($sort_by, $sort_order);
        
$query = $this->db->join('film_category', 'film_category.film_id = film.film_id');

$query = $this->db->join('category', 'film_category.category_id = category.category_id');

if (strlen($query_array['title'])) {
    $query->like('title', $query_array['title']);
}

if (strlen($query_array['category'])) {
    $query->where('category', $query_array['category']);
}

$data['films'] = $query->get('film', 20, $this->uri->segment(6));

If I only put 'film' (without the join() calls), it cannot find 'category' column in another table.

Do you have category column within your table I think it should be name instead of category — Narendrasingh Sisodia
– Narendrasingh Sisodia, Commented Aug 21, 2015 at 14:19
yes, column name is "name" instead of "category", however, even if i change it to $query->where('name', $query_array['category']); same thing: error msg: Unknown column 'name' in 'where clause' SELECT * FROM film WHERE title LIKE '%ac%' ESCAPE '!' AND name = 'Horror' AND length > '100' — Zoe
– Zoe, Commented Aug 21, 2015 at 14:25
film_category table has 2 columns: film_id, category_id;;; category table has 2 columns: category_id, name — Zoe
– Zoe, Commented Aug 21, 2015 at 14:29

user2037979 · Accepted Answer · 2015-08-21 14:46:18Z

$this->db->select('title, name as category, rental_rate, length')->order_by($sort_by, $sort_order);
$this->db->from('film'); /*I assume that film was the table name*/
$this->db->join('film_category', 'film_category.film_id = film.film_id');
$this->db->join('category', 'category.category_id = film_category.category_id');

$query = $this->db->get();

var_dump($query);

Double check that code I added and make sure that on category table, the column is called category_id, and not just id, and that under film_category, there's a category_id column. If with the code I submitted, you still get the error, try to replace the first line with

$this->db->select('title, name, rental_rate, length')->order_by($sort_by, $sort_order);

I'm not sure if using a name that matches a table will cause a trouble with CodeIgniter and ActiveRecord.

Hope that helps.

mickmackusa · Accepted Answer · 2025-11-13 22:35:45Z

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

The SELECT clause of an SQL query is processed after the WHERE clause. This means that the alias of category for the name column isn't declared until after the WHERE clause evaluation has finished. Therefore, there is no column called category to run the condition upon.

Using HAVING can be a work around (as shown in the above linked content), but it would be more sensible to not reference the alias at all. Also concerning, you have a table name and a column alias called category which may confuse human readers of your code.

public function getFilmWithDetails(
    array $query_array = [],
    string $sort_by = 'f.title',
    string $sort_order = 'asc',
    ?int $offset = null,
    ?int $limit = 20
): array {
    if (strlen($query_array['title'] ?? '')) {
        $this->db->like('f.title', $query_array['title']);
    }
    if (strlen($query_array['category'] ?? '')) {
        $this->db->where('c.name', $query_array['category']);
    }
    return $this->db
        ->select('f.title,c.name category,f.rental_rate,f.length')
        ->join('film_category fc', 'film_id')
        ->join('category c', 'category_id')
        ->order_by($sort_by, $sort_order)
        ->get('film f', $limit, $offset)
        ->result();
}

Tips:

Do not perform database processes in your contriller; that is the model's purpose.
Do not directly access submission or request payloads directly in your model. To facilitate better model method utility, reuasability, and unit testing, pass all dynamic data in from the call sites.
Do chain your query builder methods for maximum elegance.
Do use table aliases to reduce code noise and clarify data sources.
Do use USING() to concisely relate two tables by identically named columns (when there is no ambiguity about the tables being joined).

Collectives™ on Stack Overflow

CodeIgniter query builder script with two joined tables causes error: "Unknown column 'category' in 'where clause'"

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related