Support » Plugins and Hacks » Hacks » Custom query filtered by category and custom field

  • Hi,
    I want to select the distinct value of custom field “portata” filtered by category and an other custom field.
    this is the query to select a custom value by category

    global $wpdb;
        $querystr2 = "
        SELECT DISTINCT wpostmeta.meta_value
        FROM $wpdb->posts wposts
    	LEFT JOIN $wpdb->postmeta wpostmeta ON wposts.ID = wpostmeta.post_id
    	LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
    	LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    
    	WHERE wpostmeta.meta_key = 'portata'
        AND $wpdb->term_taxonomy.taxonomy = 'category'
        AND $wpdb->term_taxonomy.term_id IN($variabile_c)
        ORDER BY wpostmeta.meta_value ASC
    
        ";

    how can I add on other custom field to the filter???

Viewing 7 replies - 1 through 7 (of 7 total)
  • esmi

    (@esmi)

    Forum Moderator

    Because I need DISTINCT value….

    Moderator bcworkz

    (@bcworkz)

    Then hook the “posts_distinct” filter of WP_Query and return “DISTINCT”.

    ops….my only problem was distinct….But now i can easily convert sql query in wordpress query…I try…
    Thanks!
    PS. the wordpress query are more efficient than sql query?
    thanks

    esmi

    (@esmi)

    Forum Moderator

    Generally speaking, yes.

    thanks!
    This is my query with 1 custom field filter and 1 category

    SELECT DISTINCT wpostmeta1.meta_value
       FROM $wpdb->posts wposts
         LEFT JOIN $wpdb->postmeta wpostmeta1 ON wposts.ID = wpostmeta1.post_id
         LEFT JOIN $wpdb->postmeta wpostmeta2 ON wposts.ID = wpostmeta2.post_id
         LEFT JOIN $wpdb->term_relationships ON (wposts.ID = $wpdb->term_relationships.object_id)
         LEFT JOIN $wpdb->term_taxonomy ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
       WHERE wpostmeta1.meta_key = 'portata'
         AND wpostmeta2.meta_key = 'alimentazione'
    	 AND wpostmeta2.meta_value = '$variabile_portata'
         AND $wpdb->term_taxonomy.taxonomy = 'category'
         AND $wpdb->term_taxonomy.term_id IN($variabile_c)
       ORDER BY wpostmeta1.meta_value ASC;

    Now i try convert.
    thanks

    webmasterobservatoriobioetica

    (@webmasterobservatoriobioetica)

    Hi, I have a similar problem.

    I’m making a page, and I need to order some posts belonging to two specific categories, for this, I looked around, and I have to use the category__and. So much perfect.

    The problem is that if I use the post category__and not sorted from newest to oldest, it is sorted by date of creation, the first post created the first and last end.

    code used:

    CATEGORY__IN = WORK:

    if( trim( $pagination ) == 'true' ) {
    	$paged = mysite_get_page_query();
    	$blog_query->query(array(
    		'post__in' => $post_in,
    		'category__in' => $category_in,
    		'tag__in' => $tag_in,
    		'post_type' => 'post',
    		'posts_per_page' => $showposts,
    		'paged' => $paged,
    		'offset' => $offset,
    		'ignore_sticky_posts' => 1,
    'orderby' => 'date',
    'order' => 'DESC',
    	));
    
    } else {
    
    	$blog_query->query(array(
    		'post__in' => $post_in,
    		'category__in' => $category_in,
    		'tag__in' => $tag_in,
    		'post_type' => 'post',
    		'showposts' => $showposts,
    		'nopaging' => 0,
    		'offset' => $offset,
    		'ignore_sticky_posts' => 1,
    'orderby' => 'date',
    'order' => 'DESC',
    	));
    }

    WITH CATEGORY__AND = NOT WORK

    if( trim( $pagination ) == 'true' ) {
    	$paged = mysite_get_page_query();
    	$blog_query->query(array(
    		'post__in' => $post_in,
    		'category__and' => $category_in,
    		'tag__in' => $tag_in,
    		'post_type' => 'post',
    		'posts_per_page' => $showposts,
    		'paged' => $paged,
    		'offset' => $offset,
    		'ignore_sticky_posts' => 1,
    'orderby' => 'date',
    'order' => 'DESC',
    	));
    
    } else {
    
    	$blog_query->query(array(
    		'post__in' => $post_in,
    		'category__and' => $category_in,
    		'tag__in' => $tag_in,
    		'post_type' => 'post',
    		'showposts' => $showposts,
    		'nopaging' => 0,
    		'offset' => $offset,
    		'ignore_sticky_posts' => 1,
    'orderby' => 'date',
    'order' => 'DESC',
    	));
    }

    If I use category__in, all right, if I use category__and, not working 🙁

    Can you help me?? Thanks

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Custom query filtered by category and custom field’ is closed to new replies.