Solved

mySQL / Wordpress Top 10 query

Posted on 2011-09-10
15
461 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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The purpose of this video is to demonstrate how to automatically show related posts at the bottom of a blog post in WordPress. This will be demonstrated using a Windows 8 PC. Plugin “Yet Another Related Posts Plugin” will be used. Go to your…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now