CodeIgniter Forums
Table Prefix in COUNT using Active Record - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Table Prefix in COUNT using Active Record (/thread-43319.html)



Table Prefix in COUNT using Active Record - El Forum - 07-07-2011

[eluser]Loic Sharma[/eluser]
Hello,

I think I might have found a possible bug using the Active Record system. I am trying to build a query like:

Code:
SELECT `prefix_posts`.*, COUNT(prefix_comments.id) FROM (`prefix_posts`) LEFT JOIN `prefix_comments` ON `prefix_posts`.`id` = `prefix_comments`.`post_id` GROUP BY `prefix_posts`.`id` ORDER BY `prefix_posts`.`id` DESC

To do this, I wrote the following code:

Code:
$this->db->select('posts.*, COUNT(comments.id)');
$this->db->from('posts');
$this->db->join('comments', 'posts.id = comments.post_id', 'LEFT');
$this->db->group_by('posts.id');
$this->db->order_by('posts.id', 'DESC');

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

It seems that the Active Record system does not correctly prefix the comments table in the COUNT. The query produced is:

Code:
SELECT `prefix_posts`.*, COUNT(comments.id) FROM (`prefix_posts`) LEFT JOIN `prefix_comments` ON `prefix_posts`.`id` = `prefix_comments`.`post_id` GROUP BY `prefix_posts`.`id` ORDER BY `prefix_posts`.`id` DESC

Am I doing something wrong? Or is this a bug?

Thank you for any help! Smile


Table Prefix in COUNT using Active Record - El Forum - 07-07-2011

[eluser]Loic Sharma[/eluser]
For anyone that has a similar problem, this can be temporarily 'fixed' by doing:

Code:
$this->db->select('posts.*, COUNT('.$this->db->dbprefix.'comments.id)');