Forums

orkneywd

Member

Registered: 2008-05-10

Posts: 96

Support Desk for Fluxbb mod

I've been writing a small mod for 1.2 that turns a forum into a private support desk but I'm questioning my approach to the query. In the example below the Support Desk is forum ID 1, though this would be changed to a seperate column in the *forums table

viewforum.php

{
    if  (($cur_forum['id'] == '1') AND (!$is_admmod))
    // Without "the dot" - Each user only sees their support tickets
        $sql = 'SELECT DISTINCT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to FROM '.$db->prefix.'topics AS t LEFT JOIN '.$db_prefix.'posts AS p ON t.poster=p.poster WHERE ((p.poster_id= \''.intval($pun_user['id']).'\') OR (sticky=1)) AND forum_id='.$id.' ORDER BY sticky DESC, closed ASC, '.(($cur_forum['sort_by'] == '1') ? 'posted' : 'last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];
    else 
    // Without "the dot" - Admins and Mods see all / The default, usual forum query
        $sql = 'SELECT id, poster, subject, posted, last_post, last_post_id, last_poster, num_views, num_replies, closed, sticky, moved_to FROM '.$db->prefix.'topics WHERE forum_id='.$id.' ORDER BY sticky DESC, '.(($cur_forum['sort_by'] == '1') ? 'posted' : 'last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];
}

^ Is this a suitable approach? I'm not experienced enough to write a standalone Support Desk so had to code it directly into viewforum.php. If this can be optimised I'd appreciate some pointers or reading material.

So far, logged in members can only view their tickets, Admins and Mods can view all tickets. Closed topics are pushed to the bottom of the list for members. Sticky topics are visible to all (for FAQ's and the like).

Thanks to Mattf for his "Sticky idea" I found on Punres

Last edited by orkneywd (2009-05-18 02:27:29)

Franz

Lead developer

From: Germany

Registered: 2008-05-13

Posts: 3,755

Website

Re: Support Desk for Fluxbb mod

As far as I remember, it's faster to use GROUP BY t.id rather than DISTINCT...

---

fluxbb.de | develoPHP

"As code is more often read than written it's really important to write clean code."

MattF

Member

From: South Yorkshire, England

Registered: 2008-05-06

Posts: 1,230

Website

Re: Support Desk for Fluxbb mod

As far as I remember, it's faster to use GROUP BY t.id rather than DISTINCT...

That wouldn't work for PostgreSQL though.

---

Screw the chavs and God save the Queen!

Smartys

Former Developer

Registered: 2008-04-27

Posts: 3,117

Website

Re: Support Desk for Fluxbb mod

Yes, you would have to refer to all of the columns in the GROUP BY.

elbekko

Former Developer

From: Leuven, Belgium

Registered: 2008-04-30

Posts: 1,131

Website

Re: Support Desk for Fluxbb mod

As far as I remember, it's faster to use GROUP BY t.id rather than DISTINCT...

That wouldn't work for PostgreSQL though.

And damn I hate it for that.

*wanders off mumbling*

---

Ben
SVN repository for my extensions - The thread
Quickmarks 0.5
“Question: How does a large software project get to be one year late? Answer: One day at a time!” - Fred Brooks

orkneywd

Member

Registered: 2008-05-10

Posts: 96

Re: Support Desk for Fluxbb mod

As far as I remember, it's faster to use GROUP BY t.id rather than DISTINCT...

Ran a few tests and sounds correct, thanks

Total query time: 0.00254 Secs - Group By - Generated in 0.031 seconds, 6 queries executed
Total query time: 0.00259 Secs - Distinct - Generated in 0.032 seconds, 6 queries executed

That wouldn't work for PostgreSQL though.

Bugger. I don't even have a PostgreSQL setup, I've only ever worked in mysql but don't want people receiving errors if they try installing this on postgre.

Yes, you would have to refer to all of the columns in the GROUP BY.

$sql = 'SELECT t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to FROM '.$db->prefix.'topics AS t LEFT JOIN '.$db_prefix.'posts AS p ON t.poster=p.poster WHERE ((p.poster_id= \''.intval($pun_user['id']).'\') OR (sticky=1)) AND forum_id='.$id.' GROUP BY t.id, t.poster, t.subject, t.posted, t.last_post, t.last_post_id, t.last_poster, t.num_views, t.num_replies, t.closed, t.sticky, t.moved_to ORDER BY sticky DESC, closed ASC, '.(($cur_forum['sort_by'] == '1') ? 'posted' : 'last_post').' DESC LIMIT '.$start_from.', '.$pun_user['disp_topics'];

I assume their will be negative repercussions if not all columns are refered to in the GROUP BY?

Last edited by orkneywd (2009-05-18 19:17:51)

Smartys

Former Developer

Registered: 2008-04-27

Posts: 3,117

Website

Re: Support Desk for Fluxbb mod

The query won't work in PostgreSQL. It implements a different version of the SQL standard which states that in a group by statement, all non-aggregate columns must be listed.

Franz

Lead developer

From: Germany

Registered: 2008-05-13

Posts: 3,755

Website

Re: Support Desk for Fluxbb mod

Total query time: 0.00254 Secs - Group By - Generated in 0.031 seconds, 6 queries executed
Total query time: 0.00259 Secs - Distinct - Generated in 0.032 seconds, 6 queries executed

Talk about a huge performance gain...

---

fluxbb.de | develoPHP

"As code is more often read than written it's really important to write clean code."

Dr.Jeckyl

Member

From: :(){:|:&};:

Registered: 2008-04-30

Posts: 113

Website

Re: Support Desk for Fluxbb mod

Any update to this downliner? I'm interested in changing out my employers online suggestion box and would like to use something a bit more tailored than what we have.

---

GroundBranch
Want to learn more? Click me.

Dr.Jeckyl

Member

From: :(){:|:&};:

Registered: 2008-04-30

Posts: 113

Website

Re: Support Desk for Fluxbb mod

Anything?

---

GroundBranch
Want to learn more? Click me.