1

I have made a plugin that uses a CPT and a few metadatas. The plugin comes in two versions A and B.

Version A has one post meta less then version B called 'score'. When users update the plugin from A to B I need the score meta to be created for all custom posts for version B to work properly.

This is the insert query I have used:

global $wpdb;

$wpdb->query(
    "
    INSERT INTO $wpdb->postmeta (post_id,meta_key,meta_value)
    SELECT p.ID, 'score' AS meta_key, 0 AS meta_value
    FROM $wpdb->posts AS p
    WHERE p.post_type = 'my_cpt'
    "
);

This query works to add the new metadata but will create duplicates if runned multiple times. What should I do to make the query add the metadata only if it does not exist already?

Thanks for any possible solution.

2 Answers 2

1

To anyone interested this is the query that worked for me:

INSERT INTO $wpdb->postmeta (post_id,meta_key,meta_value)
SELECT p.ID, 'score' AS meta_key, 0 AS meta_value
FROM $wpdb->posts AS p
WHERE p.post_type = 'my_cpt'
AND p.ID NOT IN (
    SELECT p2.ID 
    FROM $wpdb->posts AS p2
    INNER JOIN $wpdb->postmeta AS pm
    ON pm.post_id = p2.ID
    WHERE pm.meta_key = 'score'
)

But after reading this article I preferred to split the query in two for better performance:

global $wpdb;

//Get all posts which already have the metadata
$not_in = $wpdb->get_results( 
    "
    SELECT p.ID
    FROM $wpdb->posts AS p
    INNER JOIN $wpdb->postmeta AS pm
    ON pm.post_id = p.ID
    WHERE pm.meta_key = 'score'
    AND p.post_type = 'my-cpt'
    AND p.post_status NOT IN ('draft,auto-draft')",
    ARRAY_A
);

//Make results into string
$not_in = ! empty($not_in) ? implode( ',', array_column( $not_in, 'ID' ) ) : '';

//Insert new metadata
$wpdb->query(
    "
    INSERT INTO $wpdb->postmeta (post_id,meta_key,meta_value)
    SELECT p.ID, 'score' AS meta_key, 0 AS meta_value
    FROM $wpdb->posts AS p
    WHERE p.post_type = 'my-cpt'
    AND p.ID NOT IN ( $not_in )
    "
);
4
  • The article you linked is from 2010, are you sure it's state of art? Aug 7, 2017 at 20:56
  • I am not sure it is still relevant but I preferred to avoid a subquery at this point as I received no better suggestion. I have also included the query with subquery into my answer for anyone interested.
    – freejack
    Aug 8, 2017 at 11:33
  • I mean you should at least try profile those queries and stick with simplest approach (which is one query) if there's no difference in performance. Keep it simple! Aug 8, 2017 at 11:41
  • Thanks НЛО, but I am not really expert to know how to profile queries... I will have to find out!! :)
    – freejack
    Aug 8, 2017 at 12:26
0

You only want to INSERT for posts, that do not have this postmeta yet. So you need to change your SELECT to only get those.

I just tried a couple of things and it seems tricky. What did work for me - but is an expensive query - is the following. Since this will only be run on plugin activation, the performance shouldn't be too much of a problem.

INSERT INTO $wpdb->postmeta (post_id,meta_key,meta_value)
SELECT p.ID, 'score' AS meta_key, 0 AS meta_value
FROM $wpdb->posts AS p
WHERE p.post_type = 'my_cpt'
AND WHERE p.ID NOT IN (
    SELECT $wpdb->posts AS p2
    INNER JOIN $wpdb->postmeta AS pm
    ON pm.post_id = p2.ID
    WHERE pm.meta_key = 'score'
)
3
  • Thanks I will test it soon and report. If I run the SELECT for the NOT IN in a separate query would it make any difference?
    – freejack
    Jul 28, 2017 at 18:15
  • @freejack Depends on what you mean by separate query. Most likely the answer is yes. You need the data this select returns, as to know which posts already have the post meta key set
    – kero
    Jul 29, 2017 at 7:27
  • What I mean is to run a query to select all posts with the metadata and then run the query to to insert the new metadata passing in the first query results in the NOT IN. Would it be any better or worse?
    – freejack
    Jul 29, 2017 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.