Solved

mySQL / Wordpress Top 10 query

Posted on 2011-09-10
15
478 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 60

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 60

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 60

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 60

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 60

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 60

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 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