select … where not exists

1 Views

I’m trying to make a custom query to pick up posts (they’re really products) from wordpress, where don’t have a meta_query named ‘custom_code’.
I’m trying: 

SELECT DISTINCT p.ID
FROM
    wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE
    p.post_type = 'product'
AND ( p.post_status = 'publish' OR p.post_status = 'draft' )
AND pm.meta_key = 'custom_code' AND pm.meta_key IS NULL

I tried with NOT EXISTS, but sql returns error:

Read More
SELECT DISTINCT p.ID
FROM
    wp_posts p
LEFT JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE
    p.post_type = 'product'
AND ( p.post_status = 'publish' OR p.post_status = 'draft' )
AND NOT EXISTS (
    select * from wp_postmeta as pm2
    where pm2.meta_key = 'custom_code'
)

NOTE: I need to get such posts with status ‘publish’ as posts with status ‘draft’.

ERROR returned:

Keyword unrecognized. (Near “NOT”, at position 193)

Keyword unrecognized. (Near “EXISTS”, at position 197)

Symbol (token) unexpected. (Near “(” at position 204)

CURRENT POINT

I did with WordPress API, and it works, but I need to add a LEFT JOIN to also get products with name like the name of each product returned by sentence below. This is the sentence: 

$args = array(
   'posts_per_page'   => -1,
   'post_type'      =>'product',
   'post_status'      => 'publish,draft',
   'meta_query' => array(
               array(
                 'key' => 'custom_code',
                 'compare' => 'NOT EXISTS'
              )),
);
$posts_array = get_posts( $args );

Any help?
Thanks in advance.

Post Views: 1

Related posts

2 comments

  1. Have you tried something like this

    $args = array(
   'posts_per_page'   => -1,
   'post_type'      =>'product',
   'post_status'      => 'publish,draft',
   'meta_query' => array(
               array(
                 'key' => 'custom_code',
                 'compare' => 'NOT EXISTS'
              )),
);
$posts_array = get_posts( $args );

var_dump( $posts_array );

    NOTE

    Use this code to print query

    global $wpdb;
var_dump( $wpdb->last_query);

  2. This is how a query with NOT EXITSTS could be:

    SELECT DISTINCT p.ID
FROM wp_posts AS p
WHERE p.post_type = 'product' AND 
      p.post_status ΙΝ ('publish', 'draft' ) AND 
      NOT EXISTS (select * 
                  from wp_postmeta as pm
                  where p.ID = pm.post_id AND 
                        pm.meta_key = 'custom_code')

    or, using LEFT JOIN:

    SELECT DISTINCT p.ID
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm 
   ON p.ID = pm.post_id AND pm.meta_key = 'custom_code'
WHERE p.post_type = 'product' AND 
      p.post_status ΙΝ ('publish', 'draft' ) AND 
      pm.post_id IS NULL

Comments are closed.