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.
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
Reset to default 1New 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
)
)
)
)