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.
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.