Problem with Caching Queries - Printable Version

Problem with Caching Queries - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Problem with Caching Queries (/showthread.php?tid=27593)

Problem with Caching Queries - El Forum - 02-15-2010

[eluser]Unknown[/eluser]
I keep getting an "A Database Error Occurred Error Number: 1052," but the database query that it says its running is no where in my code. It's almost as if it's caching parameters from previous queries on the page and adding to.

For example, here's my model:

Code:
function get_my_total_task_count() {

    $this->db->where('status_id <', '1');

    $this->db->where('person_id', $this->session->userdata('user_id'));

    $this->db->join('exp_taskassignments', 'task_id = exp_tasks.id');

    $query = $this->db->count_all('exp_tasks');

    return $query;

}

The complete error message says:

A Database Error Occurred
Error Number: 1052
Column 'id' in field list is ambiguous
SELECT `id`, `title` FROM (`exp_teams`) JOIN `exp_taskassignments` ON `task_id` = `exp_tasks`.`id` WHERE `status_id` < '1' AND `person_id` = '1' ORDER BY `title` asc

When I comment out the function listed above, the error goes away.

I've tried

Code:
$this->db->flush_cache();

but that's not helping either.

Thanks in advance.

Problem with Caching Queries - El Forum - 02-15-2010

[eluser]Jelmer[/eluser]
It's what the error says, "id" is ambiguous - mySQL doesn't know if id refers to a column from exp_tasks or exp_taskassignments.

I'm guessing here but I think it should be:

Code:
function get_my_total_task_count() {

    $this->db->where('exp_taskassignments.status_id <', '1');

    $this->db->where('exp_taskassignments.person_id', $this->session->userdata('user_id'));

    $this->db->join('exp_taskassignments', 'exp_taskassignments.task_id = exp_tasks.id');

    $query = $this->db->count_all('exp_tasks');

    return $query;

}

Problem with Caching Queries - El Forum - 02-15-2010

[eluser]Unknown[/eluser]
@Jelmer Thanks for your quick reply. I appreciate your help.

I retraced my steps and I think I figured it out.

Code:
$this->db->count_all();

Returns all the rows within a table.

So, clearing all my cache before I ran my function wouldn't have made a difference since it was applying the database parameters to the function executing after it. Once I changed count_all to num_rows it worked like a champ!

Here's the working code for the model for anyone looking at the post in the future:

Code:
function get_my_total_task_count() {

        $this->db->from('exp_tasks');

        $this->db->where('status_id <', '1');

        $this->db->where('person_id', $this->session->userdata('user_id'));

        $this->db->join('exp_taskassignments', 'task_id = exp_tasks.id');

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

        $results = $query->num_rows();

        return $results;

    }