WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] LEFT JOIN with query post (3 posts)

  1. henri
    Member
    Posted 3 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 3 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 3 years ago #

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

Topic Closed

This topic has been closed to new replies.

About this Topic