0

I am looking for a way to query users based on a user meta entry for an advanced custom field which has a given value and itself is from a given layout type.

For example, let's say I have the following entries in the usermeta table for a user:

a_flexible_content_field_0_status = 'value here'
a_flexible_content_field_1_status = 'some value'
a_flexible_content_field_2_status = 'more value data'

Where a_flexible_content_field is a flexible content field that has different layouts, these layouts are stored in the a_flexible_content_field usermeta row.

a_flexible_content_field = [0 => 'layoutTwo', 1 => 'layoutTwo', 2 => 'layoutOne']

Note, that the mapping of index and layout differs form user to user.

Now, how can I get users who have an usermeta row of a layout subfield with a given value and the given layout.

For example: Select all users that have an ACF field flexible_content_field_%_status of the layout layoutTwo and a value of some value.

Thanks a lot!


EDIT: The layout is not a possible value of the meta field, it is the acf_fc_layout name, that is stored along the field by ACF. It determines which layout ACF uses for that dynamic field. It holds a map of index and layout name pairs, that have to be checked in the query I am trying to write.

1 Answer 1

1

New answer


I now have a better understanding of the question thanks to OPs comment and have come up with the following. Not tested!

global $wpdb;

/**
 * @var string $field The flexible content field name.
 */
$field = 'a_flexible_content_field';

/**
 * @var string $layout The flexible layout name.
 */
$layout = 'layoutTwo';

/**
 * @var string $subfield The name of the layouts sub field.
 */
$subfield = 'status';

/**
 * @var string $subfield_value The value of the layouts sub field.
 */
$subfield_value = 'some value';

/**
 * @var array|int[] $users The array we will push matching user IDs to.
 */
$users = [];

/**
 * @var string $map_sql Query for finding all users using the `$layout`
 */
$map_sql = <<<MAPQUERY
    SELECT `user_id`, `meta_value` FROM $wpdb->usermeta 
    WHERE `meta_key` = '$field'
    AND `meta_value` LIKE '%$layout%'
    GROUP BY `user_id`
MAPQUERY;

/**
 * @var array|object[] $map_results Returns array of users and their flexible field mappings.
 */
$map_results = $wpdb->get_results( $map_sql );

foreach ( $map_results as $map_result ) {

    $user_id = (int)$map_result->user_id;

    /**
     * @var mixed|string[] $map An array layouts orders.
     */
    $map = maybe_unserialize( $map_result->meta_value );

    if ( is_array( $map ) ) {

        /**
         * @var false|int $layout_index The position/index of the desired layout.
         */
        $layout_index = array_search( $layout, $map );

        if ( $layout_index !== false ) {

            /**
             * @var string $meta_key Build the specific layout meta key e.g. `a_flexible_content_field_1_status`
             */
            $meta_key = $field . '_' . $layout_index . '_' . $subfield;

            /**
             * @var string $user_sql Query if user has any layout subfield matching desired value.
             */
            $user_sql = <<<USERQUERY
    SELECT COUNT(*) FROM $wpdb->usermeta 
    WHERE `meta_key` = '$meta_key'
    AND `meta_value` = '$subfield_value'
    AND `user_id` = $user_id
USERQUERY;

            $user_result = $wpdb->get_var( $user_sql );

            // User has matching values, add them to array.
            if ( $user_result > 0 ) {
                $users[] = $user_id;
            }
        }

    }
}

The result should be an array ($users) of user IDs that use the layout layoutTwo and has a subfield key of status and value some value.


Old answer


If I understand correctly, all you need to do is perform a simple query on the usermeta table, like so...

global $wpdb;

$sql = <<<USERQUERY
    SELECT `user_id` FROM $wpdb->usermeta 
    WHERE `meta_key` LIKE 'a_flexible_content_field_%_status'
    AND `meta_value` IN ( 'layoutTwo', 'some value' )
    GROUP BY `user_id`
USERQUERY;

$user_ids = array_map( function( $row ) {
    return $row->user_id;
}, $wpdb->get_results( $sql ) );

The above will return an array of user IDs that have meta fields with a key like a_flexible_content_field_%_status and if the values are either layoutTwo or some value.

Example output for $user_ids

Array
(
    [0] => 1
    [1] => 3
    [2] => 9
)

Need the meta keys and values?

This should do the trick...

global $wpdb;

$sql = <<<USERQUERY
    SELECT `user_id`, `meta_key`, `meta_value` FROM $wpdb->usermeta 
    WHERE `meta_key` LIKE 'a_flexible_content_field_%_status'
    AND `meta_value` IN ( 'layoutTwo', 'some value' )
USERQUERY;

$results = $wpdb->get_results( $sql );

Example output for $results

Array
(
    [0] => stdClass Object
        (
            [user_id] => 1
            [meta_key] => a_flexible_content_field_0_status
            [meta_value] => some value
        )
    [1] => stdClass Object
        (
            [user_id] => 1
            [meta_key] => a_flexible_content_field_1_status
            [meta_value] => layoutTwo
        )
    [2] => stdClass Object
        (
            [user_id] => 3
            [meta_key] => a_flexible_content_field_0_status
            [meta_value] => layoutTwo
        )
    [3] => stdClass Object
        (
            [user_id] => 9
            [meta_key] => a_flexible_content_field_0_status
            [meta_value] => some value
        )
)

As you can see, if a user matches the condition more than once they will have multiple results in the returned array. You could combine them with something like:

$combined = [];

foreach ( $results as $result ) {
    $user_id = $result->user_id;
    $meta_key = $result->meta_key;
    $meta_value = $result->meta_value;

    if ( !array_key_exists( $user_id, $combined ) ) {
        $combined[ $user_id ] = [
            'user_id' => $user_id,
            'values' => []
        ];
    }

    $combined[ $user_id ][ 'values' ][] = [
        'key' => $meta_key,
        'value' => $meta_value
    ];
}

$combined = array_values( $combined );

Example output for $combined

Array
(
    [0] => Array
        (
            [user_id] => 1
            [values] => Array
                (
                    [0] => Array
                        (
                            [key] => a_flexible_content_field_0_status
                            [value] => some value
                        )

                    [1] => Array
                        (
                            [key] => a_flexible_content_field_1_status
                            [value] => layoutTwo
                        )

                )

        )

    [1] => Array
        (
            [user_id] => 3
            [values] => Array
                (
                    [0] => Array
                        (
                            [key] => a_flexible_content_field_0_status
                            [value] => layoutTwo
                        )

                )

        )

    [2] => Array
        (
            [user_id] => 9
            [values] => Array
                (
                    [0] => Array
                        (
                            [key] => a_flexible_content_field_0_status
                            [value] => some value
                        )

                )

        )

)
3
  • Thank you for your answer and all the details. It's not exactly what I'm looking for. A custom field of the field type flexible_content in ACF produces a row for each entry that is created. All entries have a numeric index, and the index is linked to the layout name of the layout used for the individual entry. So, there have to be some sort of selection of the layout based on the entry in the SQL query. You have an idea if and how something like this could be done in SQL? Some sort of getting the index and comparing it with the index value stored in the layout map is needed I guess.
    – David Wolf
    Feb 2 at 18:36
  • 1
    @DavidWolf I see! ACF does store a layout map in the database, however, the problem is that the layout map varies per user so a single database query won't be enough. I've updated my answer with a proof of concept that will hopefully steer you in the right direction.
    – Levi Cole
    Feb 3 at 11:18
  • WOW! Your code looks so wonderful, and it's working beautifully. Thank you so much. You're legendary.
    – David Wolf
    Feb 3 at 19:20

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.