get_post_meta bringing back results, but $wpdb->postmeta doesn't

Question

I'm trying to discover if post exists, based on post_title & metadata post_language. Until now, the best way I discovered, was using methods appearing in posts like this one, this one, or this one.

Thing is this, that when I am calling directly get_post_meta method, I get the meta property value I was looking for

get_post_meta($post_exists_id, 'post_lang')[0] //returns "en" as expected

Furthermore, when I run my custom post_exists method, it will return me an ID.

function especialidades_post_exists( $title ) {
    global $wpdb;
    $query = "
        SELECT p.ID
        FROM $wpdb->posts AS p
        INNER JOIN $wpdb->postmeta AS pm
        WHERE 1=1
        AND (p.post_title = %s)
        AND (p.post_type = 'especialidades')
    ";
    return (int) $wpdb->get_var( 
        $wpdb->prepare( $query, $title ) 
    );
}

But if I will add a postmeta lookup into the method, it will fail, It will not find the postmeta property, and the query will return 0.

function especialidades_post_exists( $title, $language ) {
    global $wpdb;
    $query = "
        SELECT p.ID
        FROM $wpdb->posts AS p
        INNER JOIN $wpdb->postmeta AS pm
        WHERE 1=1
        AND (p.post_title = %s)
        AND (pm.post_lang = %s)
        AND (p.post_type = 'especialidades')
    ";
    return (int) $wpdb->get_var( 
        $wpdb->prepare( $query, $title, $language ) 
    );
}

What am I doing wrong? What am I missing? The postmeta property is there (as I showed before by running get_post_meta), so how come it does not find it?

I also have tried to apply this code, but still, it doesn't return me the desired result.

$another_query_args = array(
   'name' => $item_name,
   'post_type' => 'especialidades',
   'meta_query'    => array (
      array (
         'key'     => 'post_lang',
         'value'   => $language,
         'compare' => '='
      )
   ),
);
$wp_query = new WP_Query( $another_query_args );

The problem is that I got 3 languages: PT, ES & EN. It's always returning me the first result (PT), even though I have 3 posts pf the 3 languages. I wish to check only if post of specific language exists.

If someone will be able to share me some light, on what's wrong in my method, I'll appreciate it a lot! Thank you.

Answer 1

Your SQL query is invalid regarding the WP database structure.

wp_postmeta table has two columns that will help you here. meta_key : the name of your meta (post_lang in your case) meta_value : the value of your meta (en, es, ...)

Your SQL query should look like this instead. What changed is that you need to query the wp_postmeta.meta_key column to match your meta name and wp_postmeta.meta_value to match your language.

SELECT p.ID
FROM wp_posts AS p
INNER JOIN wp_postmeta AS pm
WHERE 1=1
AND (p.post_title = %s)
AND (p.post_type = 'especialidades')
AND (pm.meta_key = 'post_lang')
AND (pm.meta_value = %s)

You could also add the JOIN condition to retrieve only the rows that you need.

SELECT p.ID
FROM wp_posts AS p
INNER JOIN wp_postmeta AS pm ON (p.ID = pm.post_id AND pm.meta_key = 'post_lang' AND pm.meta_value = %s)
WHERE 1=1
AND (p.post_title = %s)
AND (p.post_type = 'especialidades')

You can try to run this query in your database management tool to see how it works. You can also add a more "human friendly" SELECT clause to better understand the links between the tables.

SELECT p.ID, p.post_title, pm.meta_key, pm.meta_value