Support

Account

Solving

Query posts by user relationship field

- MarcusW
- June 6, 2018 at 6:12 am
Hello,

I have a custom post type with a user relationship field. The user field allows for multiple values.

I am attempting to query all posts with that post type for which the user field contains the ID of the current user.

As it is being saved as a serialized array, I am trying to query it with this code:
```
$args      = array(
		'meta_key'       => 'time_start',
		'orderby'        => 'meta_value',
		'order'          => 'ASC',
		'post_type'      => $post_type,
		'post_status'    => 'publish',
		'posts_per_page' => - 1,
		'meta_query'     => array(
			array(
				'key'     => 'shift_volunteers',
				'value'   => '"' . $current_user_id . '"',
				'compare' => 'LIKE'
			),
		)
	);

	$my_query = new WP_Query( $args );
```
The query always returns 0 entries.

In the database, the value is saved e.g. as:

a:3:{i:0;s:1:"2";i:1;i:1;i:2;i:3;}

That’s a representation of…
```
Array
(
    [0] => 2
    [1] => 1
    [2] => 3
)
```
What am I doing wrong?

Value in meta query should contain only variable, like this


		'meta_query'     => array(
			array(
				'key'     => 'shift_volunteers',
				'value'   => $current_user_id,
				'compare' => 'LIKE'
			),
		)

Tested – work like a charm 🙂

- John Huebner
- September 9, 2018 at 12:20 am
@marcusw I don’t see anything wrong with your query that should cause it to not function. The value of the field should have quotes around it, otherwise a value like this would also return posts
```
a:3:{i:0;s:1:"5";i:1;s:1:"1";i:2;s:1:"3";}
```
if the user id you are querying for is “2”, without the quotes the array index 2 would return this post.

The only thing I can think of by looking at your query args is that either $post_type or $current_user_id is incorrect.

Another cause of your issue would be if you originally saved values to the field when it was set to only allow a single user and then you changed the field to allow multiple users. In this case the field value will be holding an integer instead of an array.

This change to the meta query might correct for that
```
'meta_query'     => array(
  'relation' => 'OR',
  array(
    'key'     => 'shift_volunteers',
    'value'   => '"' . $current_user_id . '"',
    'compare' => 'LIKE'
  ),
  array(
    'key'     => 'shift_volunteers',
    'value'   => $current_user_id,
    'compare' => '='
  ),
),
```
- mtv.jcil
- November 1, 2018 at 12:11 pm
I think this might be a bug with ACF Pro – I ran into the same issue. Relationship fields don’t appear to be saving correctly when the fields are attached to the User object.

An ACF relationship field on a Post saves as:
a:1:{i:0;s:3:”777″;}

While the same relationship field, made as a fresh field group, on a User saves as:
a:1:{i:0;s:3:777;}

I just spent 4 hours trying to figure out what was wrong with my meta query until I finally checked the database on a whim, and realized that the value isn’t saved the standard way to the User object (user_meta table).

Is there a reason why it saves in a different format to the User format than it does to the Post format?
- John Huebner
- November 1, 2018 at 11:27 pm
I just did a test of this and both user and relationship fields are saving string values in quotes in both the usermeta and postmeta table. Do you have any other plugins installed or filters created that could be interfering with or modifying the values of these relationships?

This is actually a function of serializing the data before it is saved, so I’m a little confuse as to what could be causing it. In fact, the second value you posted a:1:{i:0;s:3:777;} will not unserialize and causes an error.
- jayesh.magare
- January 8, 2021 at 3:04 pm
How to list the blog posts where id is either author id or participant id ACF user field in wordpress
- haydeningham
- September 10, 2021 at 5:14 am
I also have this issue and believe it to be a bug with ACF. I have a created a bidirectional relationship between two post types using two relationship fields. I have been querying these fields using WP_Query as you would normally wrapping the value (in this case a post ID) with double quotes.

However customers have told me that the query is not selecting the correct number of posts. When I looked into this I noticed that sometimes the array values are not being saved with double quotes within the serialised array. See screenshot of phpmyadmin.

Any ideas on what could be causing this would be great, it’s bringing me great stress and frustration at the moment.

Also, @hube2 as a work around I wondered if I could query both '"' . $post_ID . '"' as you would normally but also ':' . $post_ID . ';' or am I likely to run into issues with selecting the wrong posts due to the numbers in the serialised array?
Screenshot-2....png
- John Huebner
- September 10, 2021 at 8:35 am
@haydeningham If the values in the DB are being serialized as integers then there is a 99% chance this is being done by something other than ACF. A filter, or possibly calling update_field() with an array value containing integer ID values rather than strings.

I have run into this in the past when building my bidirectional relationship plugin. When getting getting values of the field using get_post_meta() I need to convert all values in the array to integers before working and then convert them all back to strings before updating. Since you mention bidirectional fields it’s possible that this is the cause of your problem.
- haydeningham
- September 10, 2021 at 8:43 am
@hube2 Thanks for the very quick response John, I appreciate your time.

Okay, so you are saying that every time I use update_field() or update_post_meta() I need to pass the IDs as strings and then every time I use get_field() or get_post_meta() I should convert the values to integers?

If so, I’ll go through and make this change in my bidirectional relationship code and also whenever I interact with either field elsewhere in my plugin.
- John Huebner
- September 10, 2021 at 8:49 am
If you use update_field(), no, you do not. If you use update_post_meta() then yes, you do. WP is doing the serialization, or more precisely, PHP. It only serializes what it is passed and will not convert integers to strings.

@hube2 okay thanks John, here is my code. Do I just wrap the update_post_meta value in quotes?

/**
   * The relationship between the class and swimmer for confirmed classes.
   *
   * @since    1.0.0
   */  
  public function class_swimmer_relationship($value, $post_id, $field) {

		// set the two fields that you want to create
		// a two way relationship for
		// these values can be the same field key
		// if you are using a single relationship field
		// on a single post type
	
		// the field key of one side of the relationship
		$key_a = 'field_spal_swimmer_classes';
		// the field key of the other side of the relationship
		// as noted above, this can be the same as $key_a
		$key_b = 'field_spal_class_swimmers';
	
		// figure out wich side we're doing and set up variables
		// if the keys are the same above then this won't matter
		// $key_a represents the field for the current posts
		// and $key_b represents the field on related posts
		if ($key_a != $field['key']) {
			// this is side b, swap the value
			$temp = $key_a;
			$key_a = $key_b;
			$key_b = $temp;
		}
	
		// get both fields
		// this gets them by using an acf function
		// that can gets field objects based on field keys
		// we may be getting the same field, but we don't care
		$field_a = acf_get_field($key_a);
		$field_b = acf_get_field($key_b);
	
		// set the field names to check
		// for each post
		$name_a = $field_a['name'];
		$name_b = $field_b['name'];
	
		// get the old value from the current post
		// compare it to the new value to see
		// if anything needs to be updated
		// use get_post_meta() to a avoid conflicts
		$old_values = get_post_meta($post_id, $name_a, true);
		// make sure that the value is an array
		if (!is_array($old_values)) {
			if (empty($old_values)) {
				$old_values = array();
			} else {
				$old_values = array($old_values);
			}
		}
		// set new values to $value
		// we don't want to mess with $value
		$new_values = $value;
		// make sure that the value is an array
		if (!is_array($new_values)) {
			if (empty($new_values)) {
				$new_values = array();
			} else {
				$new_values = array($new_values);
			}
		}
	
		// get differences
		// array_diff returns an array of values from the first
		// array that are not in the second array
		// this gives us lists that need to be added
		// or removed depending on which order we give
		// the arrays in
	
		// this line is commented out, this line should be used when setting
		// up this filter on a new site. getting values and updating values
		// on every relationship will cause a performance issue you should
		// only use the second line "$add = $new_values" when adding this
		// filter to an existing site and then you should switch to the
		// first line as soon as you get everything updated
		// in either case if you have too many existing relationships
		// checking end updated every one of them will more then likely
		// cause your updates to time out.
		//$add = array_diff($new_values, $old_values);
		$add = $new_values;
		$delete = array_diff($old_values, $new_values);
	
		// reorder the arrays to prevent possible invalid index errors
		$add = array_values($add);
		$delete = array_values($delete);
	
		if (!count($add) && !count($delete)) {
			// there are no changes
			// so there's nothing to do
			return $value;
		}
	
		// do deletes first
		// loop through all of the posts that need to have
		// the recipricol relationship removed
		for ($i=0; $i<count($delete); $i++) {
			$related_values = get_post_meta($delete[$i], $name_b, true);
			if (!is_array($related_values)) {
				if (empty($related_values)) {
					$related_values = array();
				} else {
					$related_values = array($related_values);
				}
			}
			// we use array_diff again
			// this will remove the value without needing to loop
			// through the array and find it
			$related_values = array_diff($related_values, array($post_id));
			// insert the new value
			update_post_meta($delete[$i], $name_b, $related_values);
			// insert the acf key reference, just in case
			update_post_meta($delete[$i], '_'.$name_b, $key_b);
		}
	
		// do additions, to add $post_id
		for ($i=0; $i<count($add); $i++) {
			$related_values = get_post_meta($add[$i], $name_b, true);
			if (!is_array($related_values)) {
				if (empty($related_values)) {
					$related_values = array();
				} else {
					$related_values = array($related_values);
				}
			}
			if (!in_array($post_id, $related_values)) {
				// add new relationship if it does not exist
				$related_values[] = $post_id;
			}
			// update value
			update_post_meta($add[$i], $name_b, $related_values);
			// insert the acf key reference, just in case
			update_post_meta($add[$i], '_'.$name_b, $key_b);
		}
	
		return $value;
	
	} // end function acf_reciprocal_relationship
	
	/**
   * The relationship between the class and swimmer for provisional classes.
   *
   * @since    1.0.0
   */  
	function class_swimmer_provisional_relationship($value, $post_id, $field) {

		// set the two fields that you want to create
		// a two way relationship for
		// these values can be the same field key
		// if you are using a single relationship field
		// on a single post type
	
		// the field key of one side of the relationship
		$key_a = 'field_spal_swimmer_provisional_classes';
		// the field key of the other side of the relationship
		// as noted above, this can be the same as $key_a
		$key_b = 'field_spal_class_provisional_swimmers';
	
		// figure out wich side we're doing and set up variables
		// if the keys are the same above then this won't matter
		// $key_a represents the field for the current posts
		// and $key_b represents the field on related posts
		if ($key_a != $field['key']) {
			// this is side b, swap the value
			$temp = $key_a;
			$key_a = $key_b;
			$key_b = $temp;
		}
	
		// get both fields
		// this gets them by using an acf function
		// that can gets field objects based on field keys
		// we may be getting the same field, but we don't care
		$field_a = acf_get_field($key_a);
		$field_b = acf_get_field($key_b);
	
		// set the field names to check
		// for each post
		$name_a = $field_a['name'];
		$name_b = $field_b['name'];
	
		// get the old value from the current post
		// compare it to the new value to see
		// if anything needs to be updated
		// use get_post_meta() to a avoid conflicts
		$old_values = get_post_meta($post_id, $name_a, true);
		// make sure that the value is an array
		if (!is_array($old_values)) {
			if (empty($old_values)) {
				$old_values = array();
			} else {
				$old_values = array($old_values);
			}
		}
		// set new values to $value
		// we don't want to mess with $value
		$new_values = $value;
		// make sure that the value is an array
		if (!is_array($new_values)) {
			if (empty($new_values)) {
				$new_values = array();
			} else {
				$new_values = array($new_values);
			}
		}
	
		// get differences
		// array_diff returns an array of values from the first
		// array that are not in the second array
		// this gives us lists that need to be added
		// or removed depending on which order we give
		// the arrays in
	
		// this line is commented out, this line should be used when setting
		// up this filter on a new site. getting values and updating values
		// on every relationship will cause a performance issue you should
		// only use the second line "$add = $new_values" when adding this
		// filter to an existing site and then you should switch to the
		// first line as soon as you get everything updated
		// in either case if you have too many existing relationships
		// checking end updated every one of them will more then likely
		// cause your updates to time out.
		//$add = array_diff($new_values, $old_values);
		$add = $new_values;
		$delete = array_diff($old_values, $new_values);
	
		// reorder the arrays to prevent possible invalid index errors
		$add = array_values($add);
		$delete = array_values($delete);
	
		if (!count($add) && !count($delete)) {
			// there are no changes
			// so there's nothing to do
			return $value;
		}
	
		// do deletes first
		// loop through all of the posts that need to have
		// the recipricol relationship removed
		for ($i=0; $i<count($delete); $i++) {
			$related_values = get_post_meta($delete[$i], $name_b, true);
			if (!is_array($related_values)) {
				if (empty($related_values)) {
					$related_values = array();
				} else {
					$related_values = array($related_values);
				}
			}
			// we use array_diff again
			// this will remove the value without needing to loop
			// through the array and find it
			$related_values = array_diff($related_values, array($post_id));
			// insert the new value
			update_post_meta($delete[$i], $name_b, $related_values);
			// insert the acf key reference, just in case
			update_post_meta($delete[$i], '_'.$name_b, $key_b);
		}
	
		// do additions, to add $post_id
		for ($i=0; $i<count($add); $i++) {
			$related_values = get_post_meta($add[$i], $name_b, true);
			if (!is_array($related_values)) {
				if (empty($related_values)) {
					$related_values = array();
				} else {
					$related_values = array($related_values);
				}
			}
			if (!in_array($post_id, $related_values)) {
				// add new relationship if it does not exist
				$related_values[] = $post_id;
			}
			// update value
			update_post_meta($add[$i], $name_b, $related_values);
			// insert the acf key reference, just in case
			update_post_meta($add[$i], '_'.$name_b, $key_b);
		}
	
		return $value;
	
	} // end function acf_reciprocal_relationship

- haydeningham
- September 10, 2021 at 9:19 am
@hube2 also, what are you thoughts on my earlier query of using ':' . $post_ID . ';'?

My plugin users in total have thousands of rows in the database that are effected by this so I’m not only trying to think how to stop it from happening in the future but also get the query to work for the customers that do have serialised arrays missing the quotes.
- John Huebner
- September 10, 2021 at 10:25 am
That won’t really work because if you are searching for the post ID of 1 it will match 1, 10, 213, 891. All of these fields in the DB need to be updated to have strings, so you need to fix the source of the issue and then re-save all the posts.

The simplest way is to pass your array through strval() before updating
```
value = array_map('strval', $value);
```
- haydeningham
- September 10, 2021 at 11:02 am
@hube2 I agree I need to update all the values however I don’t think that is right..

If the post ID was 1

'meta_value' => ':' . $post_ID . ';' would only match exactly :1; not all the numbers you listed?

Obviously I won’t have a low post ID like 1 they’re more likely to be in the 100s and there’s only likely to be a few post IDs maximum saved in each field value.

I will look at looping through and re-saving the values to the database.
- John Huebner
- September 10, 2021 at 12:05 pm
I missed your question about 'meta_value' => ':' . $post_ID . ';'

But I did look at this. Looking back to your data in the database, meta ID 5823 that meta query for post ID of 1 would match i:1;s:3:"857";. To be honest I can’t say how much this will affect things. I have with post IDs in the 6 digit range. In this case I’d only be safe looking for posts ID > 6.

I also have clients with > 20 posts in a relationship field so I would only be safe if the post ID is greater than 20 or 30 or it would match the a:22 portion of the serialized array. You could potentially limit this by using I missed your question about 'meta_value' => 'i:' . $post_ID . ';' but that would not eliminate matching array indexes that match post ID values.

I have never liked serialized data as a storage mechanism, but that’s WP.

Viewing 15 posts - 1 through 15 (of 15 total)

The topic ‘Query posts by user relationship field’ is closed to new replies.

Advanced Custom Fields

Support

Account

Query posts by user relationship field

MarcusW

jasnydesign

John Huebner

mtv.jcil

John Huebner

jayesh.magare

haydeningham

John Huebner

haydeningham

John Huebner

haydeningham

haydeningham

John Huebner

haydeningham

John Huebner

Welcome

Forums

Helping Others

About

Help

Community