Modify

Opened 7 years ago

Closed 7 years ago

Last modified 6 years ago

#1071 closed defect (fixed)

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

Reported by: anonymous Owned by: Blackhex
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 7 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 7 years ago by Blackhex

  • Status changed from new to assigned

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

comment:3 Changed 7 years ago by Blackhex

  • Resolution set to fixed
  • Status changed from assigned to closed

Fixed in changeset 1941.

Add Comment

Modify Ticket

Action
as closed .
as The resolution will be set. Next status will be 'closed'.
to The owner will be changed from Blackhex. Next status will be 'closed'.
The resolution will be deleted. Next status will be 'reopened'.
Author


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

 
Note: See TracTickets for help on using tickets.