How do I extract the contents of a CPT’s custom field for all posts?

  • I need to generate a CSV report with information that is stored into a few custom fields of a CPT.

    I’m trying to use wp-cli to achieve this, because it’s an extremely powerful and versatile tool that never failed me before. Sorry if I’m approaching it as an “X-Y problem“!

    Is there a wp-cli command that will list me just the content of these custom fields, for all (or a subset) of these CPTs?

    For now all I got is

    wp post list --post_type=<MYCPT> --posts_per_page=1

    to get me the most recent post, and then

    wp post meta list <ID>

    with the ID returned in the previous command.

    This lists me all the custom fields for a single custom post. What I need, however, is the content of just a few custom fields, for all custom posts of this type.

    I suppose I can write a bash script to iterate through everything and grep just the custom fields I want, but is there an easier way?

Viewing 1 replies (of 1 total)
  • Thread Starter Rudá Almeida

    (@rudaalmeida)

    I managed to find a solution. Well, two, but only one is acceptable.

    I could pipe a few CLI commands, like this:

    wp post list --post_type=CPTNAME --field=ID | xargs -n 1 wp post meta list --format=csv | grep -E "^:digit:*,(CUSTOMFIELD1|CUSTOMFIELD2|...|CUSTOMFIELDN)" >> file.csv

    However, it takes more than 30 minutes to obtain the data from about 2000 posts.

    If we use a direct MySQL query, the data is returned in a few miliseconds.

    I had to use MySQL pivot tables, like this:

    USE <wp_database_name>;
SELECT
    post_title,
    MAX(IF(meta_key = '<metakey01>', meta_value, NULL)) AS alias01,
    MAX(IF(meta_key = '<metakey02>', meta_value, NULL)) AS alias02,
    MAX(IF(meta_key = '<metakey03>', meta_value, NULL)) AS alias02,
    ...
FROM (
SELECT post_title, meta_key, meta_value
FROM <prefix>_postmeta 
INNER JOIN <prefix>_posts
ON <prefix>_postmeta.post_id = <prefix>_posts.ID
WHERE
    meta_key = '<metakey01>' OR 
    meta_key = '<metakey02>' OR 
    meta_key = '<metakey03>' OR 
    ...) as source
GROUP BY post_title;
    • This reply was modified 7 years, 11 months ago by Rudá Almeida.
Viewing 1 replies (of 1 total)
  • The topic ‘How do I extract the contents of a CPT’s custom field for all posts?’ is closed to new replies.