Welcome Guest, Not a member yet? Register   Sign In
Table Prefix in COUNT using Active Record

[eluser]Loic Sharma[/eluser]

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

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:

$this->db->select('posts.*, COUNT(comments.id)');
$this->db->join('comments', 'posts.id = comments.post_id', 'LEFT');
$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:

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

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

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

Theme © iAndrew 2016 - Forum software by © MyBB