Modify

Opened 17 years ago

Closed 17 years ago

Last modified 16 years ago

#1071 closed defect (fixed)

Discussion Forum lists reports 0 topics if there are 0 replies.

Reported by: anonymous Owned by: Radek Bartoň
Priority: normal Component: DiscussionPlugin
Severity: normal Keywords:
Cc: Trac Release: 0.10

Description

The query used for the Forum List view does a simple join of tf and mf. Hence, if there are no messages for a forum topic, the forum topic count is 0.

I think the right fix is to replace the following query with a LEFT JOIN as follows.

I tested by taking the query from the DEBUG log (before) and changing the simple join on tf,mf to a LEFT JOIN (after). I used sqlite3 interface to confirm desired results

before:

SELECT f.id, f.name, f.author, f.time, f.moderators, f.forum_group, f.subject, f.description, tm.topics, tm.replies, tm.lastreply, tm.lasttopic FROM forum f LEFT JOIN ( SELECT tf.forum AS forum, topics, lasttopic, replies, lastreply FROM (SELECT COUNT(id) AS topics, MAX(time) AS lasttopic, forum FROM topic GROUP BY forum) tf, (SELECT COUNT(id) AS replies, MAX(time) AS lastreply, forum FROM message GROUP BY forum) mf WHERE tf.forum = mf.forum) tm ON f.id = tm.forum ORDER BY f.id ASC;

after:

SELECT f.id, f.name, f.author, f.time, f.moderators, f.forum_group, f.subject, f.description, tm.topics, tm.replies, tm.lastreply, tm.lasttopic FROM forum f LEFT JOIN ( SELECT tf.forum AS forum, topics, lasttopic, replies, lastreply FROM (SELECT COUNT(id) AS topics, MAX(time) AS lasttopic, forum FROM topic GROUP BY forum) tf LEFT JOIN (SELECT COUNT(id) AS replies, MAX(time) AS lastreply, forum FROM message GROUP BY forum) mf ON tf.forum = mf.forum) tm ON f.id = tm.forum ORDER BY f.id ASC;

Attachments (0)

Change History (3)

comment:1 Changed 17 years ago by acro

I don't know anything about queries but I can confirm the problem:
Forum lists reports 0 topics if there are 0 replies.
When you post a reply it starts working correctly.

Thank you for the plugin.

comment:2 Changed 17 years ago by Radek Bartoň

Status: newassigned

OK. I've fixed this little error already but I'm waiting for some more changes before commiting them.

comment:3 Changed 17 years ago by Radek Bartoň

Resolution: fixed
Status: assignedclosed

Fixed in changeset 1941.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The owner will remain Radek Bartoň.
The resolution will be deleted. Next status will be 'reopened'.

Add Comment


E-mail address and name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.