ACFs are a great (legacy?) way of storing additional data in a ClassicPress/Wordpress installtion. But things can get a but ugly when you have thousands of posts, each with ACFs maybe nested Repeater ACFs. Getting data out of larger installation with get_posts and WP_Query can quickly hit the limits of the DB installation and fail.
The solution? A raw, hand-crafted MySQL query:
Getting a Single Custom Field
SELECT wp_postmeta.meta_value // return the meta_value
FROM wp_posts, wp_postmeta
WHERE wp_posts.post_status = 'publish' // publish posts only
AND wp_posts.ID = wp_postmeta.post_id
AND wp_postmeta.meta_key = 'my_key' // get value from 'my_key'
Getting the results with $wpdb->get_results().
$results = $wpdb->get_results($query);
Example Output
Array
(
[0] => stdClass Object
(
[meta_value ] => 123
)
[1] => stdClass Object
(
[meta_value ] => 456
)
)
That works great, if you want to get one Custom Field, but how about multiple Custom Fields?
Getting Multiple Custom Fields
Custom fields are stored in the wp_postmeta table and defined by the meta_key and meta_value column.
SELECT p.ID, m1.meta_value as v1, m2.meta_value as v2
FROM wp_posts p
LEFT JOIN wp_postmeta m1 ON p.ID = m1.post_id
LEFT JOIN wp_postmeta m2 ON p.ID = m2.post_id
WHERE p.post_status='publish'
AND m1.meta_key = 'my_key'
AND m2.meta_key = 'my_other_key'
Is it important to note - and slightly unintuitive - that the select line also declares aliases which will be used in the rest of the query:
- The
p in p.ID is a shortcut for wp_posts
- The
m1 in m1.meta_value is a shortcut for wp_postmeta
- Same as
m2, but we want to select and JOIN different meta key we also need to have two shortcuts for wp_postmeta.
meta_value as v1 is also important. If we would not use ... as v1 then the return array would include meta_value as a key and m2 would overwrite m1. Does not have to be v1 and v2, use whatever you like.
Example Output
Array
(
[0] => stdClass Object
(
[ID] => 123
[v1] => 33
[v2] => 20130402
)
[1] => stdClass Object
(
[ID] => 456
[v1] => 22
[v2] => 20130404
)
)
Getting Multiple Custom Fields including ACF Repeater
The ACF Repeater fields also stores its values in wp_postmeta, following this schema:
repeatername_nr_fieldname. Let's say we have a repeater field called videos and a sub-field called video, the meta_keys in wp_postmeta would look like this:
videos_0_video
videos_1_video
videos_2_video
videos_3_video
...
That means we need to modify the previous query, because the we can't be sure how many meta_key we have.
SELECT p.ID, m1.meta_value as v1, m2.meta_value as v2
FROM wp_posts p
LEFT JOIN wp_postmeta m1 ON p.ID = m1.post_id
LEFT JOIN wp_postmeta m2 ON p.ID = m2.post_id
WHERE p.post_status='publish'
AND m1.meta_key REGEXP '[[:<:]]videos_[0-9]*_video[[:>:]]'
AND m1.meta_value > 0
AND m2.meta_key = 'my_key'
AND m1.meta_key REGEXP '[[:<:]]videos_[0-9]*_video[[:>:]]' is a regular expression with some MySQL-specific syntax: [[:<:]] means beginning of string and [[:>:]] stands for end of string.
Sources
