vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   Please improve my fetched forumlist by MySQL (https://vborg.vbsupport.ru/showthread.php?t=223015)

ThorstenA 09-12-2009 08:51 AM
Please improve my fetched forumlist by MySQL
 
This is how I fetch the ordered forumlist with MySQL. Do you have any suggestion to make it easiser? This code is part of another script, as the results are from each user the same, it will be cached and is therefore very fast.

Code:
SELECT l1do.displayorder AS l1do, l2do.displayorder AS l2do, l3do.displayorder AS l3do,  forum.forumid, l1do.forumid AS forumidl1, l2do.forumid AS forumidl2, l3do.forumid AS forumidl3, forum.title,

(length(parentlist) - length(REPLACE(parentlist, ",", ""))) AS levels

FROM forum

LEFT JOIN (SELECT forumid, displayorder FROM forum) AS l3do
ON l3do.forumid = IF((length(parentlist) - length(REPLACE(parentlist, ",", "")))>2,
REVERSE(mid(REVERSE(parentlist),   
LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)+1,
IF(LOCATE(",",REVERSE(parentlist),  LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)+1)<1,LENGTH(parentlist)+1,LOCATE(",",REVERSE(parentlist),  LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)+1))
- LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)-1
)),0)

LEFT JOIN (SELECT forumid, displayorder FROM forum) AS l2do
ON l2do.forumid = IF((length(parentlist) - length(REPLACE(parentlist, ",", "")))>1, 
REVERSE(mid(REVERSE(parentlist), 
LOCATE(",",REVERSE(parentlist), 
LOCATE(",",REVERSE(parentlist))+1)  +1, 
IF( LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)<1,LENGTH(parentlist)+1, LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist), 
LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))+1)) - 
LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1)-1 
)),0)

LEFT JOIN (SELECT forumid, displayorder FROM forum) AS l1do
ON l1do.forumid = REVERSE(mid(REVERSE(parentlist),
LOCATE(",",REVERSE(parentlist))+1,
IF(LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1)<1,LENGTH(parentlist)+1,LOCATE(",",REVERSE(parentlist),LOCATE(",",REVERSE(parentlist))+1))
- LOCATE(",",REVERSE(parentlist))-1
))

GROUP BY forum.forumid
HAVING levels <4
ORDER BY l1do,l2do,l3do

Lynne 09-12-2009 02:35 PM
I'm not sure what you are doing with it, but you know it's already cached and you can see how it is used on the bottom of the index.php page. Not sure if that helps out with what you are doing....

ThorstenA 09-12-2009 02:56 PM
I have profile field values connected with forums and depending which profile field a viewer has ("I am driving porsche"), he sees the connected forum with subforums ("Porsche" "-- Porsche Discussion" "-- Porsche 911") on top of the forum list.

Instead of
Quote:
General Forums
-- Discussion
Porsche
-- Porsche Discussion
-- Porsche 911 ...
He sees

Quote:
Porsche
-- Porsche Discussion
-- Porsche 911
General Forums
-- Discussion ...
The script searches for lots of profile fields and depending on them puts the for the user most interesting on the top. The script also takes user behavior into consideration:
  • subscribed forums
  • moderated forums
  • forums where you read threads
  • forums where you posted
All these forums are on the top, depending whether they might be of interest for the user or not.

In Mysql I can easily query all this together and get the perfect forum order for me instead of doing all this sorting stuff in the cache forum php list which maybe is not made for things like this.

Marco van Herwaarden 09-15-2009 01:06 PM
Please be aware that this might need multiple licenses.

ThorstenA 09-15-2009 07:49 PM
Thanks for the thought. This approach presents the forums in the same order. It is developed to present the forums in a different order (which I actually do). I am unsure how this might need multiple licences, as I only use this on one domain with the database vbulletin provides me with. There is nothing changed in the database structure.

Do you see something I could do better in the sql query? Any thoughts would be great :)


All times are GMT. The time now is 10:02 PM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01004 seconds
  • Memory Usage 1,726KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)bbcode_code_printable
  • (2)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (5)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete