[SOLVED] How to get correct number of topics in forum?

[eluser]sigork[/eluser]

Code:

$this->db->select('*');

$this->db->select('COUNT(t_topicid) AS `topics_count`');
$this->db->join('Forum_Topics', 'f_forumid = t_forumid', 'left');

$this->db->select('COUNT(p_postid) AS `posts_count`');
$this->db->join('Forum_Posts', 't_topicid = p_topicid', 'left');

$this->db->group_by('f_forumid');
$this->db->order_by('f_order', 'asc');

$data['query'] = $this->db->get('Forums');

3 tables: Forums; Forum_Topics; Forum_Posts

A working example is here.

Now it has 1 Topic w/ 2 Replies.

Statistics shows 2 Topics.

If I remove Replies:

Code:

// $this->db->select('COUNT(p_postid) AS `posts_count`');
// $this->db->join('Forum_Posts', 't_topicid = p_topicid', 'left');

the number of Topics is correct (1, not 2).

I think I tried everything...

How to add Reply Count to this query?

EDIT

I rewrote it as

Code:

$data['query'] = $this->db->query("SELECT *, COUNT(t_topicid) AS topics_count, COUNT(p_postid) AS posts_count FROM Forums LEFT JOIN Forum_Topics ON f_forumid = t_forumid LEFT JOIN Forum_Posts ON t_topicid = p_topicid GROUP BY f_forumid ORDER BY f_order ASC");

The same negative result.

EDIT

SOLVED:

Code:

$data['query'] = $this->db->query("SELECT *, COUNT(DISTINCT t_topicid) AS topics_count, COUNT(p_postid) AS posts_count FROM Forums LEFT JOIN Forum_Topics ON f_forumid = t_forumid LEFT JOIN Forum_Posts ON t_topicid = p_topicid GROUP BY f_forumid ORDER BY f_order ASC");