0

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.

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'
)
  • 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 '17 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 '17 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 '17 at 15:47
0

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 )
    "
);
  • The article you linked is from 2010, are you sure it's state of art? – НЛО Aug 7 '17 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 '17 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 '17 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 '17 at 12:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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