CODEWITHSUNDEEP
mysqlwordpressjoin
luiquao
luiquao

Reputation: 1114

Wordpress JOIN wp_post and wp_postmeta

I am trying to search through wp_post & wp_postmeta simultaneously using this query:

$querystr = "
    SELECT $wpdb->posts.* 
    FROM $wpdb->posts, $wpdb->postmeta
    WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
    AND $wpdb->postmeta.meta_key = 'City' 
    AND $wpdb->postmeta.meta_value = 'Vancouver'
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'talents'
    ORDER BY $wpdb->posts.post_date DESC
 ";

$pageposts = $wpdb->get_results($querystr, OBJECT);

As you can see I search for posts that have City=Vancouver as its meta field. But how do I add one more condition so I won't be getting Vancouver, BC and Vancouver, WA together?

smth like:

    AND ($wpdb->postmeta.meta_key = 'Country' 
    AND $wpdb->postmeta.meta_value = 'Canada')
    AND ($wpdb->postmeta.meta_key = 'City' 
    AND $wpdb->postmeta.meta_value = 'Vancouver')

Upvotes: 2

Views: 11942

Answers (2)

Julius Csurgo
Julius Csurgo

Reputation: 66

Based on the link mentioned above, I was able to create this pattern to search custom posts_types based on 3 meta_values and 1 custom taxonomy.

So my structure is:

  1. Each custom post (talents) has 3 custom fields (City, Country, Gender).
  2. Each custom post belongs to the custom taxonomy "Languages" with certain categories such as English, Spanish, Russian etc.

So the query bellow is looking for a female in Canada, Toronto who can speak French.

$querystr= "SELECT * FROM wp_posts
LEFT JOIN wp_postmeta v1 ON (wp_posts.ID = v1.post_id)
LEFT JOIN wp_postmeta v2 ON (wp_posts.ID = v2.post_id)
LEFT JOIN wp_postmeta v3 ON (wp_posts.ID = v3.post_id)
LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
LEFT JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id) 
WHERE
wp_terms.name = 'French' AND wp_term_taxonomy.taxonomy = 'Languages' AND wp_posts.post_status = 'publish' AND wp_posts.post_type = 'talents' 
AND v1.meta_value = 'Toronto'
AND v2.meta_value = 'Canada'
AND v3.meta_value = 'female' 
ORDER BY wp_posts.post_date DESC";

Upvotes: 5

Arvind Pal
Arvind Pal

Reputation: 533

I think below code may help you. if u have any query let me know.

$args = array(
'post_type' => 'talents',
'posts_per_page' => 10,
'meta_query' => array(
   array(
       'key' => 'City',
       'value' => 'Vancouver',
       'compare' =>  '=',
       'type' => 'CHAR'
        )

),
    'paged' => $paged

);
$temp = $wp_query;
$wp_query = null;


                $wp_query = new WP_Query( $args );
        <?php while (have_posts()) : the_post(); 
            global $post;
/* Your Post content Here                */

endwhile;

wp_reset_query();
/* Pagination Code */
<?php
    global $wp_query;

  $big = 999999999; // need an unlikely integer

echo paginate_links( array(
    'base' => str_replace( $big, '%#%', esc_url( get_pagenum_link( $big ) ) ),
    'format' => '?paged=%#%',
    'current' => max( 1, get_query_var('paged') ),
    'total' => $wp_query->max_num_pages,
    'prev_text'    => __('Previous'),
    'next_text'    => __('Next'),
    'add_fragment' => __($search_page_link)
) );
?>

you can also add multiple condition on meta value. Url :- https://codex.wordpress.org/Class_Reference/WP_Query

Upvotes: 0

Related Questions