0

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.

3
  • Is there a reason you really needed a separate table? Why not use custom fields (postmeta?) Nov 28, 2010 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, 2010 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.) Nov 28, 2010 at 23:36

1 Answer 1

1

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;
}
13
  • 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, 2010 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, 2010 at 23:35
  • I'm very sorry Rarst, I still don't fully understand. Can you please share an example?
    – wpStudent
    Nov 27, 2010 at 23:47
  • Updated answer with more written out example.
    – Rarst
    Nov 27, 2010 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, 2010 at 0:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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