SQL Avoiding the use of "counters" columns

Yii 1.1 General Discussion
1

Hi @ all,

I’m trying to avoid usage of “counter” columns but I wonder if is that possible or not.

In my case a “section” HAS_MANY “topics” and I’m selecting the “sections”.

Here is an example of such queries I tested without success :


SELECT `t`.`id` AS `t0_c0`,

COUNT(DISTINCT topic.id) AS topicCount,

COUNT(DISTINCT post.id) AS postCount FROM `w3_forum_sections` `t`....

That works well but I need to tell SQL to count topics for each t.id.

It currently count the total number of topics (from all sections)

I tried also :

COUNT(DISTINCT topic.id) AS topicCount ON(t.id=topic.sectionId)

How I can apply a condition @ "COUNT(DISTINCT tablename)" ?

2

You should do:




SELECT `t`.`id` AS `t0_c0`,

COUNT(DISTINCT topic.id) AS topicCount,

COUNT(DISTINCT post.id) AS postCount FROM `w3_forum_sections` `t`....

GROUP BY t.id
3

So I wonder if this way is better than a static dedicated counter column that we’ll have to update @ each DELETE/INSERT INTO.

What do you think about ?

4

A static field is probably a lot faster.