WordPress.org

1. henri
   Member
   Posted 2 years ago #
   

   Hi all,

   I want to filter posts on a page via a page template and using some custom fields. I want to check if the post had a specific custom field and after I want to order post by another custom field. Here is my code :

   <?php	
   
   $total = "
   SELECT *
   FROM wp_posts
   LEFT JOIN wp_postmeta AS pm1 ON(wp_posts.ID = pm1.post_id AND pm1.meta_key = 'custom_field_disponibilite')
   LEFT JOIN wp_postmeta AS pm2 ON(wp_posts.ID = pm2.post_id AND pm2.meta_key = 'custom_field_product_reduction')
   where pm1.meta_key = 'custom_field_disponibilite'
   and pm1.meta_value = 'DISPO'
   and pm2.meta_key = 'custom_field_product_reduction'
   and pm2.meta_value != '0'
   ORDER BY CAST($wpdb->postmeta.pm2.meta_value AS DECIMAL(10,2)) DESC";
   
   $totalposts = $wpdb->get_results($total, OBJECT);
   $ppp = intval(get_query_var('posts_per_page'));
   $wp_query->found_posts = count($totalposts);
   $wp_query->max_num_pages = ceil($wp_query->found_posts / $ppp);
   $on_page = intval(get_query_var('paged'));
   if($on_page == 0){ $on_page = 1; }
   $offset = ($on_page-1) * $ppp;
   
   $wp_query->request = "SELECT * FROM $wpdb->posts
   LEFT JOIN wp_postmeta AS pm1 ON(wp_posts.ID = pm1.post_id AND pm1.meta_key = 'custom_field_disponibilite')
   LEFT JOIN wp_postmeta AS pm2 ON(wp_posts.ID = pm2.post_id AND pm2.meta_key = 'custom_field_product_reduction')
   where pm1.meta_key = 'custom_field_disponibilite'
   and pm1.meta_value = 'DISPO'
   and pm2.meta_key = 'custom_field_product_reduction'
   and pm2.meta_value != '0'
   
   ORDER BY CAST($wpdb->postmeta.pm2.meta_value AS DECIMAL(10,2)) DESC LIMIT $ppp OFFSET $offset";
   
   $pageposts = $wpdb->get_results($wp_query->request, OBJECT);
   
   ?>

   Thanks for your help!

2. vtxyzzy
   Member
   Posted 2 years ago #
   

   Using filters to query_posts might work for you:

   function mam_posts_join ($join) {
      global $mam_global_join;
      if ($mam_global_join) $join .= " $mam_global_join";
      return $join;
   }
   function mam_posts_where ($where) {
      global $mam_global_where;
      if ($mam_global_where) $where .= " $mam_global_where";
      return $where;
   }
   function mam_posts_orderby ($orderby) {
      global $mam_global_orderby;
      if ($mam_global_orderby) $orderby = $mam_global_orderby;
      return $orderby;
   }
   add_filter('posts_join','mam_posts_join');
   add_filter('posts_where','mam_posts_where');
   add_filter('posts_orderby','mam_posts_orderby');
   
   $mam_global_join = "
   LEFT JOIN $wpdb->postmeta AS pm2 ON($wpdb->posts.ID = pm2.post_id AND pm2.meta_key = 'custom_field_product_reduction') ";
   $mam_global_where = " and pm2.meta_value != '0'";
   $mam_global_orderby = "
   " CAST(pm2.meta_value AS DECIMAL(10,2)) DESC";
   
   $paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
   query_posts("meta_key=custom_field_disponibilite&meta_value=DISPO&paged=$paged");
   $mam_global_join = $mam_global_where = $mam_global_orderby = '';  // Turn off the filters

3. henri
   Member
   Posted 2 years ago #
   

   Thanks vtxyzzy you're a king it's works perfectly!

Topic Closed

This topic has been closed to new replies.