Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm trying to create a custom post query for sorting posts that have been voted "thumbs up".

I'm joining my custom "wp_post_votes" table (which stores the number of votes each post received) with the default "wp_posts" table and displaying posts WHERE votes > 1.

The WHERE cause is causing an SQL error. When I remove the posts_where filter I get no errors but all my posts are displayed regardless of their vote number.

    add_filter('posts_join', 'vote_join' );
    add_filter('posts_where', 'vote_where' );

    function vp_join( $join )
    {

      global $wpdb;

      if (isset($_GET['vote_sort'])) { //when visitor browses voted posts
        $join .= " LEFT JOIN wp_post_votes ON " . 
           $wpdb->posts . ".ID = wp_vote_posts.vote_post_id ";
      }

      return $join;
    }

    function vp_where( $where )
    {
      if (isset($_GET['vp_sort'])) {
        $where = "voted > 1";
       }

      return $where;
    }

//custom permalinks code follows...

The SQL error I get in index is..

WordPress database error: [] SELECT t., tt. FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN ('category') AND tr.object_id IN (0) ORDER BY t.name ASC

..and in my sidebar I have a recent posts widget. In that I get via $wpdb->show_errors();

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'voted > 1 ORDER BY wp_posts.post_date DESC LIMIT 0, 5' at line 1] SELECT SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts LEFT JOIN wp_vote_posts ON wp_posts.ID = wp_vote_posts.vote_post_id WHERE 1=1 voted > 1 ORDER BY wp_posts.post_date DESC LIMIT 0, 5

I noticed WHERE 1=1 voted > 1.. not sure if that's causing the error and how to fix it.

share|improve this question
    
Is there a reason you really needed a separate table? Why not use custom fields (postmeta?) –  MikeSchinkel Nov 28 '10 at 17:01
    
@MikeSchinkel the custom field table has too much data and will create too many rows for voting. My separate table is more clean and organized. –  wpStudent Nov 28 '10 at 21:10
    
Are you logging votes, or just creating a running total? If the former, I agree another table but I'd still consider maintaining the running total in custom fields, even on a per-option basis so up to 5 custom fields per post if you are using a 1..5 rating. Or you could store in one custom field as an array, but they could could do a join across posts for all posts where vote=5, for example. Otherwise if you use SQL to count the votes each time you'll end up with a very poorly performing site (I know this from specific experience related to a voting plugin I wrote.) –  MikeSchinkel Nov 28 '10 at 23:36
add comment

1 Answer

up vote 1 down vote accepted

Should be:

$where .= " AND voted > 1";

Other notes:

  • instead of hardcoding table names you should use $wpdb->prefix . 'post_votes';

  • instead of checking in global $_GET you should declare your filters as accepting two arguments and code functions accordingly, these filters pass as second argument object that contains all of query data.

Like:

add_filter('posts_where', 'vote_where', 10, 2 );

function vote_where( $where, $query ) {

    if( isset( $query->query_vars['vp_sort'] ) );
        $where .= ' AND voted > 1';

    return $where;
}
share|improve this answer
    
Thank you Rarst!! It works now :) I don't understand your second note. I have to check if the URL parameter exists to use the filter. How can I implement the two arguments you provided? –  wpStudent Nov 27 '10 at 23:32
    
Your URL parameters are parsed and stored in query object, which you can access inside your function via passed $query variable in my example above. Just try var_dump( $query ); to see everything that it contains, your custom arguments should be in there as well if used. –  Rarst Nov 27 '10 at 23:35
    
I'm very sorry Rarst, I still don't fully understand. Can you please share an example? –  wpStudent Nov 27 '10 at 23:47
    
Updated answer with more written out example. –  Rarst Nov 27 '10 at 23:53
    
Ooh I see. Thanks! I tried the code, no errors.. but nothing happens. All posts are displayed regardless of the URL paramater –  wpStudent Nov 28 '10 at 0:06
show 9 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.