• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 487
  • Last Modified:

mySQL / Wordpress Top 10 query

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
m2ew
Asked:
m2ew
  • 7
  • 6
  • 2
1 Solution
 
jeremyjared74Commented:
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
 
m2ewAuthor Commented:
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
 
jeremyjared74Commented:
Did you notice this line?

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

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
m2ewAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
MySQL uses LIMIT 5 instead of TOP 5.
0
 
m2ewAuthor Commented:
@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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
m2ewAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
Okay. You may even be able to use the LIMIT 5 on the inner query, thus reducing overhead of the outer query calculation.
0
 
m2ewAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
m2ewAuthor Commented:
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
 
m2ewAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now