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

Question

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 having data registered in the BD respecting these search criteria.

Answer 1

It seems to me that you are searching for conflicting fields in the table post_meta. In this table a record cannot have two meta_keys at the same time, so you need an expression OR 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 lines with meta_key job_category as the ones that has meta_key _viewable