Go Back   vb.org Archive > vBulletin 4 Discussion > vB4 Programming Discussions
  #1  
Old 01-02-2017, 03:47 PM
grey_goose grey_goose is offline
 
Join Date: Jun 2009
Posts: 284
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default MYSQL Query to count words written

I've got a query written that more or less counts words, that I can execute on a per-user basis because it needs the userid in WHEREs.

What I'd like to do is be able to update the entire table and run it as a cron job -- but I can't figure out how to get the userid into the subquery.
X = userid
@thisuser = where I need the userids

Code:
SET @thisuser := X;
UPDATE user 
SET words = (SELECT SUM(words)
   FROM (SELECT LENGTH(p.pagetext) - LENGTH(REPLACE(p.pagetext, ' ', ''))+1 AS words
   FROM post as p
   WHERE userid = @thisuser
  UNION ALL
  SELECT LENGTH(gm.pagetext) - LENGTH(REPLACE(gm.pagetext, ' ', ''))+1 AS words
   FROM groupmessage as gm
   WHERE postuserid = @thisuser) a)
WHERE userid = @thisuser;
I'm not sure if I'm going about this the wrong way, or just can't figure out how to fetch the userid of the current row that the query is looped on.

Suggestions?
Reply With Quote
  #2  
Old 01-02-2017, 03:49 PM
Dave Dave is offline
 
Join Date: May 2010
Posts: 2,583
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

So if I understand you correctly, you want to convert the SQL you posted to work without a hardcoded userid and run it against all users in the database?
Reply With Quote
  #3  
Old 01-02-2017, 04:21 PM
grey_goose grey_goose is offline
 
Join Date: Jun 2009
Posts: 284
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

...Yes. Although the way you asked that makes me wonder if I'm doing something dumb *lol*

I know it's pretty spendy from running it by hand, but it's a small forum.
Reply With Quote
  #4  
Old 01-02-2017, 04:24 PM
Dave Dave is offline
 
Join Date: May 2010
Posts: 2,583
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Yes this is not something you would want to do as a cronjob, as your database gets bigger, the cronjob will take more time and more load to complete.

It would be better if you run this one time and then create a hook in vBulletin that updates the `words` column every time a post is made/modified.
Reply With Quote
Благодарность от:
Dragonsys
  #5  
Old 01-02-2017, 04:33 PM
grey_goose grey_goose is offline
 
Join Date: Jun 2009
Posts: 284
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Good point. I knew my spider sense was tingling!

What about executing/updating it per user on MEMBERINFO pageview, since that's where the info is displayed? Probably add a quarter sec to page load, but I'll be touching it far less.
Reply With Quote
  #6  
Old 01-03-2017, 07:07 PM
Dave Dave is offline
 
Join Date: May 2010
Posts: 2,583
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by grey_goose View Post
Good point. I knew my spider sense was tingling!

What about executing/updating it per user on MEMBERINFO pageview, since that's where the info is displayed? Probably add a quarter sec to page load, but I'll be touching it far less.
You could approach it in 2 different ways:
1. Always update it every time the profile is visited.
2. Add it once to the login of the user, then mark the user so it does not get executed again when the user is logging in. Then update it every time the user makes a post.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:18 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04065 seconds
  • Memory Usage 2,206KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (6)post_thanks_box
  • (1)post_thanks_box_bit
  • (6)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (1)post_thanks_postbit
  • (6)post_thanks_postbit_info
  • (6)postbit
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • fetch_musername
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • post_thanks_function_fetch_thanks_bit_start
  • post_thanks_function_show_thanks_date_start
  • post_thanks_function_show_thanks_date_end
  • post_thanks_function_fetch_thanks_bit_end
  • post_thanks_function_fetch_post_thanks_template_start
  • post_thanks_function_fetch_post_thanks_template_end
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete