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:

1. The p in p.ID is a shortcut for wp_posts
2. The m1 in m1.meta_value is a shortcut for wp_postmeta
3. Same as m2, but we want to select and JOIN different meta key we also need to have two shortcuts for wp_postmeta.
4. 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

• https://wordpress.stackexchange.com/questions/237957/get-post-with-multiple-meta-keys-and-value
• https://wordpress.stackexchange.com/questions/358962/get-2-meta-values-from-meta-key-column