Compare CPT taxonomy to user’s relationship field

Hi @0rca

You are actually quite close with the IN statement, however, the ID’s are probably not rendered correctly.

You’ll need a helper function to convert the relationship field to a list of IDs.

function rel_to_ids( $value ) {
    if ( is_array( $value ) && isset( $value[0]['term_id'] ) ) {
        $ids = wp_list_pluck( $value, 'term_id' );
    }
    return implode( ',', $ids );
}

You can then use it within your shortcode like so: {@user.objektrights,rel_to_ids}.

Also validate the actual query, you can use a plugin like Query Monitor to see the queries that are made.

Cheers, Jory

Hi Jory,

thank you so much for your help. I guessed something like this, but my coding abilities are extremely basic. I have added the script and changed the shortcode, but get this error (and no output) now:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ') ) AND ( 't'.'post_type' = 'orderliste' ) AND ( 't'.'post_status' IN ( 'publ...' at line 17

This is the query:

SELECT DISTINCT 't'.*
FROM 'wphc_posts' AS 't'
LEFT JOIN 'wphc_term_relationships' AS 'rel_objektrechte'
ON 'rel_objektrechte'.'object_id' = 't'.'ID'
LEFT JOIN 'wphc_term_taxonomy' AS 'rel_tt_objektrechte'
ON 'rel_tt_objektrechte'.'taxonomy' = 'objektrechte'
AND 'rel_tt_objektrechte'.'term_taxonomy_id' = 'rel_objektrechte'.'term_taxonomy_id'
LEFT JOIN 'wphc_terms' AS 'objektrechte'
ON 'objektrechte'.'term_id' = 'rel_tt_objektrechte'.'term_id'
WHERE ( ( 'objektrechte'.'term_id' IN () )
AND ( 't'.'post_type' = 'orderliste' )
AND ( 't'.'post_status' IN ( 'publish' ) ) )
ORDER BY 't'.'menu_order', 't'.'post_title', 't'.'post_date'
LIMIT 0, 15

• This reply was modified 1 year, 6 months ago by 0rca.

Damn, the code block keeps stripping the quotation marks ??

Edit: I replaced them, now it works.

• This reply was modified 1 year, 6 months ago by 0rca.

Hi @0rca

The returned value is empty so that would be your error, however, I’m wondering why it’s empty.
You could use some debug options in the function I’ve send you to display the actual value, like var_dump($value).
Or you could return 0 as the default value in case the current user doesn’t have any metadata set.

Cheers, Jory

In order to test the function I just did [pods name=”user” template=”Users”] and this template

Name {@user.display_name}
Objectrights {@user.objektrechte}
Function {@user.objektrechte,rel_to_ids}

But I get the result 5 times:

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

Either this is unrelated and I simply don’t get it or @user is not unique?

• This reply was modified 1 year, 6 months ago by 0rca.
• This reply was modified 1 year, 6 months ago by 0rca.

[pods name="user" template="Users"] will query all users.

To restrict to the current user, use:

[pods name="user" id="{@user.ID} template="Users"]

…with define( 'PODS_SHORTCODE_ALLOW_EVALUATE_TAGS', true ); set in wp-config.php.

Hi Paul,

so the shortcode will query all existing 5 users, but output only the values of the curent user for each? Or why the 5 outputs?

To me this sounds similar to my still existing problem, i.e. that

{@user.objektrights,rel_to_ids}

does not output anything, maybe because @user is not really defined?

• This reply was modified 1 year, 6 months ago by 0rca.

[pods name="user" template="Users"] is a generic query for all users without any restriction. It will query all users and loop through the template for each found user.

[pods name="user" id="{@user.ID}" template="Users"] is the same query, but filtered to the current user. If it doesn’t work, [pods name="user" where="ID = {@user.ID}" template="Users"] may be more reliable and clear as to what is happening. (I had trouble with the first format earlier today). For either of those syntaxes to work, define( 'PODS_SHORTCODE_ALLOW_EVALUATE_TAGS', true ); must be set in wp-config.php.

Within the template, the queried user is already being iterated, so the magic tag would be {@objektrights,rel_to_ids}, not {@user.objektrights,rel_to_ids}.

Likewise, the full template should be:

Name {@display_name}
Objectrights {@objektrechte}
Function {@objektrechte,rel_to_ids}

…as {@user} always refers to the currently logged-in user. So it should be used in the shortcode to specify the where attribute, not in the template. By leaving the shortcode unspecified, then using {@user.[whatever]} within the template, it causes every user to be iterated in a loop, but the outputted variables to be related only to the logged-in user.

So, in summary, the correct shortcode is [pods name="user" where="ID = {@user.ID}" template="Users"] and the correct template is above.

• This reply was modified 1 year, 6 months ago by Paul Clark.

Hi Paul, sorry for the confusion, but I was mixing my OT question with the [pods name="user" template="Users"] thing.

My orignal problem was [pods name=”orderlist” where=”objectrights.term_id IN ({@user.objektrights})” template=”Orderlist”] not working and Jory suggested a function to convert the array of values. But so far this still doesn’t work. But here @user is not in the context of the “User” pods, but the “Orderlist” pods. hence the {@user.objektrights,rel_to_ids}

While trying to figure out why, I tested [pods name="user" template="Users"] and this actually outputs this:

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

Name Michael
Objectrights 336
Function

So something is not working as expected, it should list all 5 users, not just mine 5 times, right? I don’t really need [pods name=”user” template=”Users”] to work, I was just testing something and it turned out completely unexpected. And I have no clue if my OT problem and this one are related, my grasp of these things is limited at best. So any help is much appreciated, even more so for the OT. The other thing I am just curious about.

• This reply was modified 1 year, 6 months ago by 0rca.

Just saw your edit and now I get it, the template of my test was wrong – sorry I missed that.

I have updated the template and it works as expect now. Sorry for being a bit slow…

Now that I see {@objektrechte,rel_to_ids} working correctly. I only have to find out, why it wouldn’t work in my OT context.

• This reply was modified 1 year, 6 months ago by 0rca.

As Jory noted, it appears in the SQL you shared that the value passed to IN() is empty. This would occur if nothing has been saved to the field, or if the field comes through as a single term array instead of an array containing array(s).

[pods name="orderlist" where="objectrights.term_id IN ( {@user.objektrights,rel_to_ids} )" template="Orderlist"]

This version of the function is a bit pedantic, but should account for all cases of what could possibly be passed to $value:

function rel_to_ids( $value ) {
	if ( is_array( $value ) && is_array( $value[0] ) && isset( $value[0]['term_id'] ) ) {
		return implode( ',', wp_list_pluck( $value, 'term_id' ) );
	}elseif ( is_array( $value ) && isset( $value['term_id'] ) ) {
		return $value['term_id'];
	}
	return 0;
}

Just updated the function above to end with return 0; instead of return '';, realizing that you reported a SQL error for IN().

IN() can’t be blank, so the appropriate behavior is to pass integer 0, which will never exist as a term_id. This will cause the where query of IN( 0 ) to find nothing, but still be valid SQL. In this case, the template will not display if no term is assigned.

The elseif is a guess, and may not be necessary. I suspect magic tags might return [ 'term_id' => 123, etc... ] (a single array) instead of [ [ 'term_id' => 123, etc... ] ] (an array containing a single array) if only one term is set.

Hi Paul, thank you for taking the time to help me. What I failed to mention before was the result of my updated/corrected test. With the correct template

Name {@display_name}
Objectrights {@objektrechte}
Function {@objektrechte,rel_to_ids}

it outputs 5 users, and my user is listed like this:

Name Michael
Objectrights GFM_R_SUED_UNT_bd, GFM_R_SUED_UNT_bm, GFM_R_SUED_UNT_bs, TROOST_GBR_GMBH
Function 336,337,338,340

That means the function works and the array is correctly converted. I.e.

{@objektrechte,rel_to_ids}

lists all values (336,337,338,340).

That means on the other hand, that my shortcode is not working:

[pods name="orderlist" where="objectrights.term_id IN ( {@user.objektrights,rel_to_ids} )" template="Orderlist"]

Do you have any idea what could be going wrong?

The name of the field you’ve assigned to the user is objektrechte but in the where query you’ve written objectrights.term_id IN ( {@user.objektrights,rel_to_ids} ) instead of objectrights.term_id IN ( {@user.objektrechte,rel_to_ids} )

The taxonomy name is objectrights.
The user relationship field is objektrechte.

Another one of my earlier mistakes, in my OT I partly translated some expressions, thinking it might be clearer. Dumb idea in retrospect…

This is the real non-working shortcode

[pods name="orderliste" where="objektrechte.term_id IN ({@user.objektrechte,rel_to_ids})" template="Orderlisten"]

Since

{@objektrechte,rel_to_ids}

works as expected and since

[pods name="orderliste" where="objektrechte.term_id IN ({@user.objektrechte})" template="Orderlisten"]

outputs the first value of the array, there must be something else wrong with the expression.

Thank you for your patience with me and my misstatements.