Posted By

errkk on 10/19/10


Tagged

sql wordpress filter taxonomy custom WPQuery getposts


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

srdjan
qing606


Custom Taxonomy post listing filter for WordPress with sort options


 / Published in: PHP
 

  1. // Get custom taxonomies
  2. // Get array of taxonomies eg activity, difficulty, subject
  3. $taxonomies = get_taxonomies( array('_builtin' => false, 'public' => true),'objects' );
  4. // foreach taxonomy, get a list of options to sort by, terms, eg maths, science
  5. foreach ( $taxonomies as $tax => $tax_obj ){
  6. $terms[$tax_obj->query_var] = get_terms($tax_obj->name); // array of terms
  7. }
  8.  
  9. // check stuff in GET is a registered taxonomy
  10. $tax_args = array();
  11. foreach ( $_GET as $key => $val ){
  12. if( array_key_exists($key, $terms) ){
  13. // if its set, add an argument for get_posts
  14. if( $val ){
  15. $tax_args[$key] = $val;
  16. }
  17. }
  18. }
  19.  
  20.  
  21.  
  22. // build lines of conditions for if there are taxonomical requirements in the query string
  23. $sort_conditions = '';
  24. $required_matches = -1;
  25.  
  26. if( count( $tax_args ) ){
  27. $sort_conditions.= " WHERE 0=1 "; // add this so other stuff can just repeat ORs
  28. foreach( $tax_args as $key => $slug ){
  29. $sort_conditions.= " OR taxonomy = '$key' AND slug = '$slug' ";
  30. $required_matches ++;
  31. }
  32. }
  33.  
  34. $sql ="
  35. SELECT count(1) AS matches,t1.* FROM(
  36.  
  37. SELECT wp_posts.*, wp_term_taxonomy.taxonomy, wp_terms.slug
  38. FROM wp_posts
  39. LEFT JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id)
  40. LEFT JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
  41. LEFT JOIN wp_terms ON(wp_term_taxonomy.term_id = wp_terms.term_id)
  42. JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
  43. WHERE wp_posts.post_type = 'post'
  44. AND (wp_posts.post_status = 'publish')
  45. AND wp_postmeta.meta_key = 'priority'
  46. AND wp_posts.post_status = 'publish'
  47. ORDER BY wp_postmeta.meta_value ASC, wp_posts.post_date DESC
  48. LIMIT 0, 999) AS t1
  49.  
  50. $sort_conditions
  51. GROUP BY ID HAVING matches > $required_matches";
  52.  
  53. $listing = $wpdb->get_results( $sql );

Report this snippet  

You need to login to post a comment.