PDA

View Full Version : Query help in forumdisplay.php


Antivirus
08-01-2009, 12:40 AM
I have a mod I've written that modifies the threads shown in forumdisplay for a particular forumid. My problem however isn't with the threads which are queried, but the problem lies with navigating multiple pages.

Incorrect values are passed to the $limitlower, $limitupper, and $totalthreads vars, which basically either cause the pagenav to no longer show, or give the incorrect values to the "showing threads 1 to 1 of 1" phrase.

I have narrowed it down to the threadscount query which is run before the threadids query and the main query. Basically, the unmodified threadscount query looks something like this:


SELECT COUNT(*) AS threads, 1 AS newthread
$hook_query_fields
FROM thread AS thread
$hook_query_joins
WHERE forumid = 34
AND sticky = 0
$hook_query_where

the result of this query is shown in the attached image 'query1.jpg'


After my plugin code inserted at hook: 'forumdisplay_query_threadscount' the query looks like this:

SELECT COUNT(*) AS threads, 1 AS newthread
FROM thread AS thread
LEFT JOIN post AS p ON (p.postid = thread.firstpostid)
LEFT JOIN sc_postgroup AS sc_pg ON (sc_pg.postid = p.postid)
LEFT JOIN socialgroup AS sg ON (sg.groupid = sc_pg.groupid)
LEFT JOIN sc_status AS sc_status ON (sc_status.statusid = p.sc_statusid)
WHERE forumid = 34
AND sticky = 0

the result of the modified query is shown in the attached image 'query2.jpg'

As you can see, the end result is now grouping the incorrect way - as there are 16 results counted (as opposed to 10 as there should be).

I am pretty sure it's due to the way COUNT() is interacting with the type of JOINS I am using in the modified query, however I am not too familiar with the logic of RIGHT, INNER, OUTER, if this is indeed the problem, etc...

Any ideas here? I have been trying different types of joins, however i might be missing the correct combination.

Antivirus
08-01-2009, 01:28 AM
ok - i have definitely narrowed it down to somethign to do with this JOIN in particular:

LEFT JOIN sc_postgroup AS sc_pg ON (sc_pg.postid = p.postid)


There are sometime 0 or more records in sc_postgroup with the same postid. I need this here though because users are only able to see threads that are associated with the socialgroups they have joined. Maybe this JOiN should be replaced with a subquery?

--------------- Added 1249100198 at 1249100198 ---------------

*solved, please disregard*