Need to expand shortcodes in pod parameters to [table] shortcode #10

New Issue

glen · 2022-03-23T17:04:28Z

glen commented

2022-03-23 17:04:28 +00:00

[On behalf of https://github.com/Rooi-Els from GitHub]

So I have a pod called "application" and I want to list all applications made by the current user.
I have the following columns in TablePress:

Username Vacancy Title
{@applicant_username} {@vacancy_title}

The only shortcode I've gotten to work is the following:

[table id=2 pod_name="application" /]

But this will list all the users that have applied for all vacancies. I want to only list the applications done by the current user.

I have tried several variations of the following shortcode, but it doesn't work:

[table id=2 pod_name="application" pod_where="t.applicant_username = user_login" /]

I've also tried adding the following shortcode in php:

if ( function_exists( 'tablepress_get_table' ) ) {
add_shortcode( 'table-user-filtered', 'tablepress_filtered_table' );
function tablepress_filtered_table( $atts ) {
if ( ! is_user_logged_in() ) {
return 'Error: No user is logged in.';
}
$current_user = wp_get_current_user();
$applicant_username = $current_user->user_login;
$atts['filter'] = $applicant_username;
$atts['filter_full_cell_match'] = true;
$atts['cache_table_output'] = false;
return tablepress_get_table( $atts ) ;
}
}

and then using this shortcode:

[table-user-filtered id=2 pod_name="application" /]

but this does nothing as well.

Any help you can offer at this stage will be much appreciated!

And thanks so much for this plugin, it really ads a much needed dimension to tablepress!

Kind Regards,
Michael

[On behalf of https://github.com/Rooi-Els from GitHub] So I have a pod called "application" and I want to list all applications made by the current user. I have the following columns in TablePress: Username Vacancy Title {@applicant_username} <a href= "{@permalink, esc_url}" > {@vacancy_title} </a> The only shortcode I've gotten to work is the following: [table id=2 pod_name="application" /] But this will list all the users that have applied for all vacancies. I want to only list the applications done by the current user. I have tried several variations of the following shortcode, but it doesn't work: [table id=2 pod_name="application" pod_where="t.applicant_username = user_login" /] I've also tried adding the following shortcode in php: if ( function_exists( 'tablepress_get_table' ) ) { add_shortcode( 'table-user-filtered', 'tablepress_filtered_table' ); function tablepress_filtered_table( $atts ) { if ( ! is_user_logged_in() ) { return 'Error: No user is logged in.'; } $current_user = wp_get_current_user(); $applicant_username = $current_user->user_login; $atts['filter'] = $applicant_username; $atts['filter_full_cell_match'] = true; $atts['cache_table_output'] = false; return tablepress_get_table( $atts ) ; } } and then using this shortcode: [table-user-filtered id=2 pod_name="application" /] but this does nothing as well. Any help you can offer at this stage will be much appreciated! And thanks so much for this plugin, it really ads a much needed dimension to tablepress! Kind Regards, Michael

glen commented

2022-03-23 17:20:48 +00:00

Definitely want to get this working for you, you are definitely supposed to be able to filter with the pod_where command. It's been a while since I have set up a website with this extension (now I just use the wbesites that I made at the time...). So I don't 100% remember off the top of my head how to correctly substitute the current value of user_login into the pod_where filter, so let's first make sure it works with a literal value. In other words, pick one user in your database, suppose it's 'Jorge', and try the shortcode:

[table id=2 pod_name="application" pod_where="t.applicant_username = 'Jorge'" /]

Let me know if that shows just the records in your "application" POD where the applicant_username field is the specific value you hard-coded in.

Assuming that works, we'll figure out how to substitute in the current value of user_login.

Definitely want to get this working for you, you are definitely supposed to be able to filter with the pod_where command. It's been a while since I have set up a website with this extension (now I just use the wbesites that I made at the time...). So I don't 100% remember off the top of my head how to correctly substitute the current value of user_login into the pod_where filter, so let's first make sure it works with a literal value. In other words, pick one user in your database, suppose it's 'Jorge', and try the shortcode: [table id=2 pod_name="application" pod_where="t.applicant_username = 'Jorge'" /] Let me know if that shows just the records in your "application" POD where the applicant_username field is the specific value you hard-coded in. Assuming that works, we'll figure out how to substitute in the current value of user_login.

Rooi-Els commented

2022-03-24 08:26:34 +00:00

Hi Glen,

Thank you so much for your response, I really appreciate it!

I tried the shortcode with literal value as you suggested...

But the moment I try and use the pod_where argument, the following error message is shown where the table should be:

Database Error; SQL: SELECT DISTINCT t.* FROM wp_#####_posts AS t WHERE ( ( t.applicant_username = 'MichaelEls' ) AND ( t.post_type = 'application' ) AND ( t.post_status IN ( 'publish' ) ) ) ORDER BY t.menu_order, t.post_title, t.post_date; Response: Unknown column 't.applicant_username' in 'where clause'

I thought it might work if I change my TablePress column name to either applicant_username or t.applicant_username. This still gave me the same error message, so I changed it back.

Then I tried putting t.applicant_username in single quotations:

[table id=2 pod_name="application" pod_where = "'t.applicant_username' = 'MichaelEls'" /]

This seemed to remove the above error, but now only the Table Headings are shown with none of the applications listed under it. It does seem that we are moving in the right direction though.

Thanks again for your help!

Kind regards,
Michael

Hi Glen, Thank you so much for your response, I really appreciate it! I tried the shortcode with literal value as you suggested... But the moment I try and use the pod_where argument, the following error message is shown where the table should be: Database Error; SQL: SELECT DISTINCT `t`.* FROM `wp_#####_posts` AS `t` WHERE ( ( t.applicant_username = 'MichaelEls' ) AND ( `t`.`post_type` = 'application' ) AND ( `t`.`post_status` IN ( 'publish' ) ) ) ORDER BY `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`; Response: Unknown column 't.applicant_username' in 'where clause' I thought it might work if I change my TablePress column name to either applicant_username or t.applicant_username. This still gave me the same error message, so I changed it back. Then I tried putting t.applicant_username in single quotations: [table id=2 pod_name="application" pod_where = "'t.applicant_username' = 'MichaelEls'" /] This seemed to remove the above error, but now only the Table Headings are shown with none of the applications listed under it. It does seem that we are moving in the right direction though. Thanks again for your help! Kind regards, Michael

glen commented

2022-03-24 21:18:53 +00:00

Aha, (I think in the time since I first wrote this extension), the PODS syntax for referring to a field in your pod in the 'where' parameter has changed. Please see https://docs.pods.io/code/pods/find/find-reference-table/

Presuming that the "name" of your field (as shown in the "Edit Pod" screen for this pod under Pods Admin) is 'spplicant_username', it seems that you will either need to write pod_where="applicant_username.meta_value = 'MichaelEls'" or pod_where="d.applicant_username = 'MichaelEls'" depending on whether your pod is using "meta-based storage" or "table-based storage," respectively. (And I am not clear on how to know which a given POD is; on my test server, it was the "FIELDNAME.meta_value" notation that worked for me.

So please see if one of those works for you with a fixed value like 'MichaelEls' and then we'll worry about getting it working with a varying value.

We'll get there! When this is resolved, I will update the code and make a new release with our findings in the comments, so thanks for your help in keeping this plugin up-to-date.

Aha, (I think in the time since I first wrote this extension), the PODS syntax for referring to a field in your pod in the 'where' parameter has changed. Please see https://docs.pods.io/code/pods/find/find-reference-table/ Presuming that the "name" of your field (as shown in the "Edit Pod" screen for this pod under Pods Admin) is 'spplicant_username', it seems that you will either need to write `pod_where="applicant_username.meta_value = 'MichaelEls'"` or `pod_where="d.applicant_username = 'MichaelEls'"` depending on whether your pod is using "meta-based storage" or "table-based storage," respectively. (And I am not clear on how to know which a given POD is; on my test server, it was the "FIELDNAME.meta_value" notation that worked for me. So please see if one of those works for you with a fixed value like 'MichaelEls' and then we'll worry about getting it working with a varying value. We'll get there! When this is resolved, I will update the code and make a new release with our findings in the comments, so thanks for your help in keeping this plugin up-to-date.

Rooi-Els commented

2022-03-26 13:25:48 +00:00

Hi Glen,

So pod_where="applicant_username.meta_value = 'MichaelEls'" worked perfectly. Thanks for the update.

I tried playing around with the following varying value formats, but unfortunately nothing worked yet:

pod_where="applicant_username.meta_value = user_login"
pod_where="applicant_username.meta_value = user_login.meta_value"
pod_where="applicant_username.meta_value = user_login.user_meta"

I also tried the above with user_login at end of meta_value and user_meta. I also tried single quotations, but nothing worked so far.

Thanks so much for your help in this!

Kind regards,
Michael

Hi Glen, So pod_where="applicant_username.meta_value = 'MichaelEls'" worked perfectly. Thanks for the update. I tried playing around with the following varying value formats, but unfortunately nothing worked yet: pod_where="applicant_username.meta_value = user_login" pod_where="applicant_username.meta_value = user_login.meta_value" pod_where="applicant_username.meta_value = user_login.user_meta" I also tried the above with user_login at end of meta_value and user_meta. I also tried single quotations, but nothing worked so far. Thanks so much for your help in this! Kind regards, Michael

glen commented

2022-03-26 19:19:16 +00:00

Great, glad we are making progress. Now, we want to have the value of a PHP-level variable used in the pod_where attribute of the [table] shortcode. Unfortunately, it seems that's not directly possible:

https://toolset.com/forums/topic/using-site-global-variables/

So the question is whether there is a shortcode that gives the current user login. I can't find a built-in one, but here's an article that discusses adding one:

https://www.eruditeworks.com/2020/08/13/display-logged-in-username-on-right-top-of-your-wordpress-website/

So you could try adding that [current_user] shortcode, and then try in your use of the table shortcode writing pod_where="applicant_username.meta_value = [current_user]" (possibly you might need to put single quotes around the value, like '[current_user]', sorry I am not 100% sure on the details of PHP/Wordpress/shortcodes/PODS quoting and substitution convention). Let me know if that's something you can try, and if so, how it goes.

Great, glad we are making progress. Now, we want to have the value of a PHP-level variable used in the pod_where attribute of the `[table]` shortcode. Unfortunately, it seems that's not directly possible: https://toolset.com/forums/topic/using-site-global-variables/ So the question is whether there is a shortcode that gives the current user login. I can't find a built-in one, but here's an article that discusses adding one: https://www.eruditeworks.com/2020/08/13/display-logged-in-username-on-right-top-of-your-wordpress-website/ So you could try adding that `[current_user]` shortcode, and then try in your use of the table shortcode writing `pod_where="applicant_username.meta_value = [current_user]"` (possibly you might need to put single quotes around the value, like `'[current_user]'`, sorry I am not 100% sure on the details of PHP/Wordpress/shortcodes/PODS quoting and substitution convention). Let me know if that's something you can try, and if so, how it goes.

Rooi-Els commented

2022-03-28 17:46:11 +00:00

Hi Glen,

So I added the following shortcode for [current_user]:

add_shortcode( 'current_user' , 'wp_get_current_user_func' );

function wp_get_current_user_func( $atts ) {

	$current_user = wp_get_current_user();
	
	return $current_user->user_login;
}

And on its own, it works perfectly. The problem comes in when I add it in the pod_where argument. This is the format I used:
[table id=2 pod_name="application" pod_where="applicant_username.meta_value = '[current_user]' "/]

You can see a screenshot of the result here:
https://prnt.sc/U4105nws8P4F

Notice in my top red circle that the end of the table shortcode is "cut off". The bottom circle was just a test to see if [current_user] works on its own. So I think the end of my [current_user] brackets is treated as the end of the [table ###] shortcode.

So this led me via google searches to nested shortcodes. I think what we need to do, is wrap the pod_where argument that is defined in a do_shortcode() function.

I had a look at your php code, but couldn't figure out where the user's pod_where is taken as input to the where statement. Could you please help?

Thank you so so much!

Kind regards,
Michael

Hi Glen, So I added the following shortcode for [current_user]: ``` add_shortcode( 'current_user' , 'wp_get_current_user_func' ); function wp_get_current_user_func( $atts ) { $current_user = wp_get_current_user(); return $current_user->user_login; } ``` And on its own, it works perfectly. The problem comes in when I add it in the pod_where argument. This is the format I used: [table id=2 pod_name="application" pod_where="applicant_username.meta_value = '[current_user]' "/] You can see a screenshot of the result here: https://prnt.sc/U4105nws8P4F Notice in my top red circle that the end of the table shortcode is "cut off". The bottom circle was just a test to see if [current_user] works on its own. So I think the end of my [current_user] brackets is treated as the end of the [table ###] shortcode. So this led me via google searches to nested shortcodes. I think what we need to do, is wrap the pod_where argument that is defined in a do_shortcode() function. I had a look at your php code, but couldn't figure out where the user's pod_where is taken as input to the where statement. Could you please help? Thank you so so much! Kind regards, Michael

glen changed title from ~~Difficulty with [table] shortcode~~ to Need to expand shortcodes in pod parameters to [table] shortcode

2022-03-28 17:54:03 +00:00

glen commented

2022-03-28 17:56:32 +00:00

Ah, OK, I didn't realize that each individual shortcode had to take care of recursive calls to shortcodes. I will figure that out and make a new release as soon as I have it working. It may take a couple of days...

Rooi-Els commented

2022-03-28 18:49:06 +00:00

Hi Glen,

It would be great if you can... I am working on a deadline here, so would really appreciate your help!

Thank you so so much for all your help so far.

Kind regards,
Michael

Hi Glen, It would be great if you can... I am working on a deadline here, so would really appreciate your help! Thank you so so much for all your help so far. Kind regards, Michael

glen commented

2022-03-28 21:02:22 +00:00

Ah OK, I will try to get to this as quickly as I can. It turns out there is no way around the ] within the attribute value prematurely closing the shortcode. So I will have to make (( and )) within the attribute values translate to [ and ] first and then call do_shortcode -- unless you have a better suggestion as to what characters should translate to [ and ]. I'll let you know as soon as I have it working.

Ah OK, I will try to get to this as quickly as I can. It turns out there is no way around the `]` within the attribute value prematurely closing the shortcode. So I will have to make `((` and `))` within the attribute values translate to `[` and `]` first and then call do_shortcode -- unless you have a better suggestion as to what characters should translate to `[` and `]`. I'll let you know as soon as I have it working.

glen referenced a pull request that will close this issue

2022-03-28 23:07:38 +00:00

feat: Allow shortcodes in attribute values #11

glen closed this issue

2022-03-28 23:08:13 +00:00

glen referenced this issue from a commit

2022-03-28 23:08:13 +00:00

feat: Allow shortcodes in attribute values (#11)

glen commented

2022-03-28 23:16:40 +00:00

OK, please go ahead and upgrade to the 0.4.1 release, and see if that works for you; don't forget to use (( and )) to surround your shortcode call, e.g.,

[table id=2 pod_name="application" pod_where="applicant_username.meta_value = '((current_user))' "/]

If you encounter any further difficulties, please open a new issue in this repo, thanks!

OK, please go ahead and upgrade to the 0.4.1 release, and see if that works for you; don't forget to use `((` and `))` to surround your shortcode call, e.g., ``` [table id=2 pod_name="application" pod_where="applicant_username.meta_value = '((current_user))' "/] ``` If you encounter any further difficulties, please open a new issue in this repo, thanks!

Rooi-Els commented

2022-03-29 06:02:01 +00:00

Wow! It works!

Thank you so much for this, I appreciate it so so much!

It's really great being part of a coder community that helps others...

Kind regards,
Michael

Wow! It works! Thank you so much for this, I appreciate it so so much! It's really great being part of a coder community that helps others... Kind regards, Michael

Sign in to join this conversation.