Luan Piegas

LinkedIn

Exporting Custom Post Type data and Custom Fields (ACF)

Written by

Luan Piegas

in

This approach combines the necessary data from both the wp_posts and wp_postmeta tables using JOIN and UNION clauses.

Single SQL Export Query

This query will select all the necessary posts (your custom post type and ACF field definitions) and all of their associated metadata in one go. Remember to replace 'your_cpt_name' with the actual slug of your custom post type.

-- Select posts and their metadata for the specified Custom Post Type
SELECT p.*, pm.*
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id
WHERE p.post_type = 'your_cpt_name'

UNION ALL

-- Select ACF Field Groups/Fields and their metadata
SELECT p.*, pm.*
FROM wp_posts AS p
LEFT JOIN wp_postmeta AS pm ON p.ID = pm.post_id
WHERE p.post_type IN ('acf-field-group', 'acf-field');

How It Works

  • LEFT JOIN: This command links the wp_posts table (aliased as p) with the wp_postmeta table (aliased as pm). It fetches every post from the wp_posts table that meets the criteria and all of its corresponding metadata from wp_postmeta.
  • WHERE p.post_type = 'your_cpt_name': This is the first part of the query. It filters the selection to include only the posts belonging to your specific custom post type.
  • UNION ALL: This command combines the results from the first SELECT statement with the results of the second one. UNION ALL is generally faster than UNION because it doesn’t check for duplicate rows, which isn’t necessary for this type of backup.
  • WHERE p.post_type IN ('acf-field-group', 'acf-field'): This is the second part of the query. It filters the selection to include only the “posts” that define your ACF field groups and the individual fields within them. This is crucial for ensuring the structure of your custom fields is preserved when you import the data.

Conclusion

Running this single, consolidated query is more efficient as it reduces the number of separate operations performed on the database, resulting in a faster export. When you import the resulting .sql file, it will correctly reconstruct your custom post type entries, their ACF data, and the ACF field group definitions on the new site.

More posts