Custom query filtered by category and custom field

  • pettedemon

    (@pettedemon)


    11 years, 6 months 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???

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

    (@esmi)

    11 years, 6 months ago

    Why not use WP_Query?

    http://codex.wordpress.org/Function_Reference/WP_Query

    Thread Starter pettedemon

    (@pettedemon)

    11 years, 6 months ago

    Because I need DISTINCT value….

    Moderator bcworkz

    (@bcworkz)

    11 years, 6 months ago

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

    Thread Starter pettedemon

    (@pettedemon)

    11 years, 6 months 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

    esmi

    (@esmi)

    11 years, 6 months ago

    Generally speaking, yes.

    Thread Starter pettedemon

    (@pettedemon)

    11 years, 6 months 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

    webmasterobservatoriobioetica

    (@webmasterobservatoriobioetica)

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

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