LEFT JOIN with query post

  • Resolved henri

    (@riri23)


    13 years, 2 months 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!

Viewing 2 replies - 1 through 2 (of 2 total)
  • vtxyzzy

    (@vtxyzzy)

    13 years, 2 months 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
    Thread Starter henri

    (@riri23)

    13 years, 2 months ago

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

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘LEFT JOIN with query post’ is closed to new replies.