Implement 2 search criteria in the query (Wodpress / MySQL)

Question

Implement 2 search criteria in the query (Wodpress / MySQL)

Navigation

#1 by (1 votes)

0

Within this query how do I add 2 more search criteria $wpdb->postmeta.meta_key = '_viewable' and $wpdb->postmeta.meta_value = 'yes' ?

$query10 = $wpdb->get_results("
    SELECT * FROM $wpdb->usermeta LEFT JOIN $wpdb->users ON($wpdb->users.ID = $wpdb->usermeta.user_id) LEFT JOIN $wpdb->posts ON($wpdb->posts.post_author = $wpdb->users.ID)
    LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
    WHERE $wpdb->posts.post_type = 'noo_resume'
    AND $wpdb->postmeta.meta_key = '_job_category'
    AND $wpdb->postmeta.meta_value LIKE '%{$key->term_id}%'
    AND $wpdb->usermeta.meta_key = '_jm_candidate_field_clocknow_user_btn'
    AND $wpdb->usermeta.meta_value = 'value_1'
");

I'm trying but is returning an empty array, even though I have data registered in the DB respecting these search criteria.

mysql wordpress

asked by anonymous 22.02.2017 / 15:46

1 answer

Why this query does not work in Oracle? How to change getRealPath () save location? Photocam primefaces

score 1 · Accepted Answer

It seems to me that you are looking for conflicting fields in the post_meta table. In this table a record can not have two meta_keys at the same time, so you need a OR expression to fetch both values separately, something like this:

WHERE $wpdb->posts.post_type = 'noo_resume'
AND ( 
  ( $wpdb->postmeta.meta_key = '_job_category' AND $wpdb->postmeta.meta_value LIKE '%{$key->term_id}%' )
  OR
  ( $wpdb->postmeta.meta_key = '_viewable' AND $wpdb->postmeta.meta_value = 'yes' )
)
AND $wpdb->usermeta.meta_key = '_jm_candidate_field_clocknow_user_btn'
AND $wpdb->usermeta.meta_value = 'value_1'

So the query will bring from the table postmeta both the lines with meta_key job_category and those with meta_key _viewable