Solved

mySQL / Wordpress Top 10 query

Posted on 2011-09-10
15
467 Views
Last Modified: 2012-05-12
Hi I'm trying to update this top 5 query to work with wordpress & custom mysql tables. I originally wrote it for mssql / asp but I am now getting a syntax error near '*, (SELECT COALESCE...'  in line 1
Select TOP (5) x.*, (SELECT COALESCE(ratingsum,1) / COALESCE(filesinglevote,1)) as ratingavg 
FROM ( SELECT (SELECT meta_value FROM wp_postmeta AS G WHERE G.meta_value = a.filename AND G.meta_key = 'clarge_image') AS filename , (SELECT SUM(rating) AS Expr1 FROM wp_galleryvote AS GV WHERE GV.filename = a.filename) AS 
ratingsum, (SELECT COUNT(*) AS Expr2 FROM wp_galleryvote AS GV WHERE GV.filename = a.filename) AS filesinglevote, (SELECT COUNT(*) AS Expr3 FROM wp_galleryvote AS GV WHERE GV.filename is not null) AS filevotestotal, (SELECT 
post_title FROM wp_posts AS G INNER JOIN wp_postmeta P ON G.ID = P.post_id WHERE P.meta_value = a.filename AND G.meta_key = 'clarge_image' ) AS thecaption FROM wp_galleryvote AS a GROUP by filename) x WHERE x.filename IS NOT NULL 
ORDER BY ratingavg DESC, filesinglevote DESC

Open in new window

0
Comment
Question by:m2ew
  • 7
  • 6
  • 2
15 Comments
 
LVL 23

Expert Comment

by:jeremyjared74
ID: 36517763
To Display the top posts in a WordPress site you would use something like this:

<?php
$popular_posts = new WP_Query('orderby=comment_count&posts_per_page=10'); ?>
<?php while ($popular_posts->have_posts()) : $popular_posts->the_post(); ?>
<?php
  if ( has_post_thumbnail() ) {
?>
<a href="<?php the_permalink(); ?>" title="<?php the_title(); ?>">
<?php 
the_post_thumbnail('thumbnail', array(
  'alt' => ''.get_the_title().'', 
  'title' => ''.get_the_title().''
  )); 
  ?>
</a>
<a class="popular-post-title" href="<?php the_permalink(); ?>" title="<?php the_title(); ?>"><?php the_title(); ?></a>
<?php } else ?> 
  <p>in <span class="popular-categ"><?php $category = get_the_category(); $categLink = get_category_link($category[0]->cat_ID); ?> 
  <a href="<?php echo $categLink; ?>" title="<?php $category[0]->cat_name; ?>">
  <?php echo $category[0]->cat_name; ?></a></span></p>
<div class="entry">
  <?php the_content(); ?>
</div>
  <?php endwhile; ?>
</div>

Open in new window


To get comment counts, you can use:
<?php $commentscount = get_comments_number(); echo $commentscount; ?>

Open in new window

0
 

Author Comment

by:m2ew
ID: 36519173
Hey jeremyjared74,
I get the wordpress output, but its more the mysql portion. I'm interested in as it is used to calculate the top 5 results from polling count / average. Once the query is working, then I will have no problem outputting it.
0
 
LVL 23

Expert Comment

by:jeremyjared74
ID: 36519446
Did you notice this line?

$popular_posts = new WP_Query('orderby=comment_count&posts_per_page=10');

Open in new window

0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:m2ew
ID: 36519550
Yes. However, the query is based off of getting the top rated pics from the a custom gallery that I have setup. It has to pull the top 5 results for the best average vote ie. ratingavg

The only wp function I'm using is the wpdb->get_results
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36519736
MySQL uses LIMIT 5 instead of TOP 5.
0
 

Author Comment

by:m2ew
ID: 36519967
@mwvisa1
I switch to limit so the query now starts with

Select x.*, (SELECT ....) x WHERE x.filename IS NOT NULL
ORDER BY ratingavg DESC, filesinglevote DESC LIMIT 5

However I'm now getting mysql serve has gone away error
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36519994
Hmm. That is a lot of correlated sub-queries to the same table. Makes me think you can do this differently. Server my have "gone away" or timed out because of the undue complexity.You may want to take a look at condition aggregates (I have an article here on EE); although, when I re-read the conditions, they seem like they are straight aggregates by the outer queries grouping, so unclear as to your intent there.
SELECT x.*
     , (SELECT COALESCE(ratingsum,1) / COALESCE(filesinglevote,1)) as ratingavg 
FROM ( 
   SELECT (
      SELECT meta_value 
      FROM wp_postmeta AS G 
      WHERE G.meta_value = a.filename 
      AND G.meta_key = 'clarge_image'
   ) AS filename, (
      SELECT SUM(rating) AS Expr1 
      FROM wp_galleryvote AS GV 
      WHERE GV.filename = a.filename
   ) AS ratingsum, (
      SELECT COUNT(*) AS Expr2 
      FROM wp_galleryvote AS GV 
      WHERE GV.filename = a.filename
   ) AS filesinglevote, (
      SELECT COUNT(*) AS Expr3 
      FROM wp_galleryvote AS GV 
      WHERE GV.filename is not null
   ) AS filevotestotal, (
      SELECT post_title 
      FROM wp_posts AS G 
      INNER JOIN wp_postmeta P ON G.ID = P.post_id 
      WHERE P.meta_value = a.filename 
      AND G.meta_key = 'clarge_image' 
   ) AS thecaption 
   FROM wp_galleryvote AS a 
   GROUP by filename
) x 
WHERE x.filename IS NOT NULL 
ORDER BY ratingavg DESC, filesinglevote DESC
LIMIT 5

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36520035
BTW, I didn't change the code, just posted formatted so you can see what is going on better and to exemplify my point on the redundancy. Let's take this a step at a time, shall we.

First:
   SELECT (
      SELECT SUM(rating) AS Expr1 
      FROM wp_galleryvote AS GV 
      WHERE GV.filename = a.filename
   ) AS ratingsum, (
      SELECT COUNT(*) AS Expr2 
      FROM wp_galleryvote AS GV 
      WHERE GV.filename = a.filename
   ) AS filesinglevote, (
      SELECT COUNT(*) AS Expr3 
      FROM wp_galleryvote AS GV 
      WHERE GV.filename is not null
   ) AS filevotestotal
   FROM wp_galleryvote AS a 
   GROUP by filename

Open in new window


Can be rewritten:
   SELECT SUM(rating) AS ratingsum
        , COUNT(1) AS filesinglevote
        -- grab current count and add to running total
        , @cnt := @cnt + COUNT(1) 
   FROM wp_galleryvote AS a, (SELECT @cnt := 0) AS r
   GROUP by filename

Open in new window


Just run the second query and ensure those totals meet your needs. Then we can move on to adding the rest.
0
 

Author Comment

by:m2ew
ID: 36520060
I'll have to play around with this.
Basically I need to grab the top 5 results for a polling query
filename, ratingavg, filesinglevote
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36520092
Okay. You may even be able to use the LIMIT 5 on the inner query, thus reducing overhead of the outer query calculation.
0
 

Author Comment

by:m2ew
ID: 36520108
SELECT x.*
     , (SELECT COALESCE(ratingsum,1) / COALESCE(filesinglevote,1)) as ratingavg
FROM (
   SELECT (
      SELECT meta_value
      FROM wp_postmeta AS G
      WHERE G.meta_value = a.filename
      AND G.meta_key = 'clarge_image'
   ) AS filename, (
     SELECT SUM(rating) AS ratingsum, COUNT(1) as filesinglevote, @cnt := @cnt + COUNT(1)
         FROM wp_galleryvote AS a, (SELECT @cnt := 0) AS r
        GROUP by filename
   ) AS thecaption
   FROM wp_galleryvote AS a
   GROUP by filename
) x
WHERE x.filename IS NOT NULL
ORDER BY ratingavg DESC, filesinglevote DESC
LIMIT 5
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36520120
I am sorry. I just wanted you to run the query I gave you and make sure the totals matched your data correctly. Once it does, then you can filter down as needed. Couple housekeeping things. Do you actually use the COUNT() of all the votes including the ones where filename is null? I just noticed the ratingavg is over filesinglevote, which by the makes that the same as AVG(rating) in the original query.
0
 

Author Comment

by:m2ew
ID: 36520121
This seemed to work much better, thanks for showing me the redundancy example
 SELECT x.*
     , (SELECT COALESCE(ratingsum,1) / COALESCE(filesinglevote,1)) as ratingavg FROM 
            (SELECT filename, SUM(rating) AS ratingsum, COUNT(1) as filesinglevote, @cnt := @cnt +                 COUNT(1) 
   	FROM wp_galleryvote AS a, (SELECT @cnt := 0) AS r
        WHERE contest = 'Look A Like'
  	GROUP by filename) x

Open in new window

0
 

Author Comment

by:m2ew
ID: 36520133
Yes I'm using COUNT() to calculate the average. In this case I removed the filename is null as with the new db structure there are no null values in the filename field
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36520142
But you can just use AVG(rating) to calculate the average. :) But I was really asking about the running count that is telling you the count of all rows not just for a given file name.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
The purpose of this video is to demonstrate how to manually back up a WordPress Database. This will be demonstrated using a Windows 8 PC. The Host used will be IPage.com Log into your Hosting account. IPage will be used for demonstration : Locat…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question