WordPress.org

Ready to get started?Download WordPress

Forums

Custom query filtered by category and custom field (8 posts)

  1. pettedemon
    Member
    Posted 1 year ago #

    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???

  2. esmi
    Forum Moderator
    Posted 1 year ago #

  3. pettedemon
    Member
    Posted 1 year ago #

    Because I need DISTINCT value....

  4. bcworkz
    Member
    Posted 1 year ago #

    Then hook the "posts_distinct" filter of WP_Query and return "DISTINCT".

  5. pettedemon
    Member
    Posted 1 year ago #

    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

  6. esmi
    Forum Moderator
    Posted 1 year ago #

    Generally speaking, yes.

  7. pettedemon
    Member
    Posted 1 year ago #

    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

  8. webmasterobservatoriobioetica
    Member
    Posted 1 year ago #

    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

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags

No tags yet.