Filter, datefield and rest

[adcommunicatie]

Description

Please see my post on WordPress forum: https://wordpress.org/support/topic/filter-datefield-and-rest/#post-15019021

We are encountering the following errors when updating from 2.7.X to the latest 2.8.3:

• The default Filter buttons throws an SQL-error:

Database Error; SQL: SELECT DISTINCTt.* FROMwp_postsAStLEFT JOINwp_postmetaASdateONdate.meta_key= 'post_date' ANDdate.post_id=t.IDLEFT JOINwp_icl_translationsASwpml_translationsONwpml_translations.element_id=t.IDANDwpml_translations.element_type= 'post_download' ANDwpml_translations.language_code= 'nl' LEFT JOINwp_icl_languagesASwpml_languagesONwpml_languages.code=wpml_translations.language_codeANDwpml_languages.active= 1 WHERE ( (t.post_statusIN ( 'publish' ) ) AND (t.post_type= 'download' ) AND (wpml_languages.codeIS NOT NULL ) ) AND (t.product_filter= 'mwall_basic' ORt.product_filterLIKE '%"mwall\\_basic"%' ) ORDER BYdate.meta_valueDESC,t.menu_order,t.post_title,t.post_date; Response: Unknown column 't.product_filter' in 'where clause'

• The custom PODS fields are not working anymore with the REST API

/wp-json/wp/v2/podsname?per_page=100

Standard fields like id, title are working. But the REST API is not giving us custom fields.

Version 2.7.X (gives all fields):

"type": "huurders",
    "title": {
      "rendered": "Beheer"
    },
    "content": {
      "rendered": "",
      "protected": false
    },
    "template": "",
    "pagetype": "hpage",
    "pw": "xxxx",
    "appnr": "0",
    "huur": [],
    "service": [],
    "bwater": [],
    "bstroom": [],
    "was": [],

Version 2.8.X (does not show any custom fields):

 "slug": "beheer",
    "status": "publish",
    "type": "huurders",
    "title": {
      "rendered": "Beheer"
    },
    "content": {
      "rendered": "",
      "protected": false
    },

Testing instructions

1. Import package
2. Create a test item
3. Set-up a default filter page with example: $get_downloads = pods('download')->find(array('orderby' => 'date DESC', 'limit' => '-1')); $get_downloads->filters( array( 'fields' => array( 'product_filter', 'soort_informatie' ), 'label' => 'Filteren' ));
4. Enter the button "Filteren"; you will get the SQL error.

For REST API please try the URL.

Pods version

2.8.X

WordPress Environment

### wp-core ###

version: 5.8.1
site_language: nl_NL
user_language: nl_NL
timezone: +00:00
permalink: /%postname%/
https_status: false
multisite: false
user_registration: 0
blog_public: 0
default_comment_status: open
environment_type: production
user_count: 2
dotorg_communication: true

### wp-active-theme ###

name:
version: 1.0
author: ICT Ready
author_website: https://www.ictready.nl/
parent_theme: none
theme_features: core-block-patterns, widgets, post-thumbnails, menus, widgets-block-editor, woocommerce, wc-product-gallery-zoom, wc-product-gallery-lightbox, wc-product-gallery-slider
auto_update: Uitgeschakeld

### wp-themes-inactive (3) ###

Twenty Nineteen: version: 1.9, author: het WordPress team (latest version: 2.1), Auto-updates uitgeschakeld
Twenty Twenty: version: 1.6, author: het WordPress team (latest version: 1.8), Auto-updates uitgeschakeld
Twenty Twenty-One: version: 1.1, author: het WordPress team (latest version: 1.4), Auto-updates uitgeschakeld

### wp-plugins-active (16) ###

Contact Form 7: version: 5.5.2, author: Takayuki Miyoshi, Auto-updates uitgeschakeld
Disable Gutenberg: version: 2.5.1, author: Jeff Starr, Auto-updates uitgeschakeld
Loco Translate: version: 2.5.5, author: Tim Whitlock, Auto-updates uitgeschakeld
Pods - Custom Content Types and Fields: version: 2.7.31, author: Pods Framework Team (latest version: 2.8.3), Auto-updates uitgeschakeld
Product Variations Swatches for WooCommerce: version: 1.0.1.16, author: VillaTheme, Auto-updates uitgeschakeld
Simple Custom Post Order: version: 2.5.6, author: Colorlib, Auto-updates uitgeschakeld
WooCommerce: version: 5.8.0, author: Automattic, Auto-updates uitgeschakeld
WPBakery Page Builder: version: 6.5.0, author: Michael M - WPBakery.com (latest version: 6.7), Auto-updates uitgeschakeld
WPML Media: version: 2.6.3, author: OnTheGoSystems, Auto-updates uitgeschakeld
WPML Multilingual CMS: version: 4.4.9, author: OnTheGoSystems, Auto-updates uitgeschakeld
WPML String Translation: version: 3.1.7, author: OnTheGoSystems, Auto-updates uitgeschakeld
WPML Translation Management: version: 2.10.5, author: OnTheGoSystems, Auto-updates uitgeschakeld
YITH WooCommerce Catalog Mode: version: 2.2.0, author: YITH, Auto-updates uitgeschakeld
YITH WooCommerce Request A Quote Premium: version: 3.0.4, author: YITH (latest version: 3.4.0), Auto-updates uitgeschakeld
Yoast Duplicate Post: version: 4.1.2, author: Enrico Battocchi & Team Yoast, Auto-updates uitgeschakeld
Yoast SEO: version: 17.4, author: Team Yoast, Auto-updates uitgeschakeld

### wp-plugins-inactive (6) ###

Autoptimize: version: 2.9.2, author: Frank Goossens (futtta), Auto-updates uitgeschakeld
MainWP Child: version: 4.1.8, author: MainWP, Auto-updates uitgeschakeld
What The File: version: 1.5.4, author: Never5, Auto-updates uitgeschakeld
Wordfence Security: version: 7.5.6, author: Wordfence, Auto-updates uitgeschakeld
WP Fastest Cache: version: 0.9.5, author: Emre Vona, Auto-updates uitgeschakeld
YITH Request a Quote for WooCommerce: version: 2.2.0, author: YITH, Auto-updates uitgeschakeld

### wp-media ###

image_editor: WP_Image_Editor_GD
imagick_module_version: Niet beschikbaar
imagemagick_version: Niet beschikbaar
imagick_version: Niet beschikbaar
file_uploads: File uploads is turned off
post_max_size: 8M
upload_max_filesize: 2M
max_effective_size: 2 MB
max_file_uploads: 20
gd_version: 2.2.5
gd_formats: GIF, JPEG, PNG, WebP, BMP, XPM
ghostscript_version: 9.27

### wp-filesystem ###

wordpress: writable
wp-content: writable
uploads: writable
plugins: writable
themes: writable

### pods ###

pods-server-software: Apache
pods-session-save-path-exists: Ja
pods-session-save-path-writable: Ja
pods-session-max-lifetime: 1440
pods-opcode-cache-apc: Ja
pods-opcode-cache-memcached: Nee
pods-opcode-cache-opcache: Ja
pods-opcode-cache-redis: Nee
pods-object-cache-apc: Ja
pods-object-cache-apcu: Ja
pods-object-cache-memcache: Nee
pods-object-cache-memcached: Nee
pods-object-cache-redis: Nee
pods-memory-current-usage: 36,966M
pods-memory-current-usage-real: 6,000M
pods-network-wide: Nee
pods-developer: Nee
pods-tableless-mode: Nee
pods-light-mode: Nee
pods-strict: Nee
pods-allow-deprecated: Ja
pods-api-cache: Ja
pods-shortcode-allow-evaluate-tags: Nee

{"meta":{"version":"2.7.31","build":1635775251},"pods":{"188":{"id":188,"name":"download","label":"Downloads","description":"","type":"post_type","storage":"meta","object":"","alias":"","fields":{"document":{"id":189,"name":"document","label":"Document","description":"","help":"","class":"","type":"file","weight":0,"pick_object":"custom-simple","pick_val":"","sister_id":"","required":"1","file_format_type":"single","file_uploader":"attachment","file_attachment_tab":"upload","file_edit_title":"1","file_show_edit_link":"0","file_linked":"0","file_limit":"0","file_restrict_filesize":"10MB","file_type":"any","file_field_template":"rows","file_add_button":"Document toevoegen","file_modal_title":"Voeg een document toe","file_modal_add_button":"Document toevoegen","file_wp_gallery_output":"0","file_wp_gallery_link":"post","file_wp_gallery_columns":"1","file_wp_gallery_random_sort":"0","file_wp_gallery_size":"thumbnail","pick_post_status":["publish"],"admin_only":"0","restrict_role":"0","restrict_capability":"0","hidden":"0","read_only":"0","roles_allowed":["administrator"],"rest_read":"0","rest_write":"0","rest_pick_response":"array","rest_pick_depth":"2","unique":"0","_wpml_location_migration_done":"1","file_upload_dir":"wp","file_upload_dir_custom":"","file_allowed_extensions":""},"product_filter":{"id":530,"name":"product_filter","label":"Product","description":"","help":"","class":"","type":"pick","weight":1,"pick_object":"custom-simple","pick_val":"","sister_id":"","pick_custom":"mwall_basic|M-Wall Basic\nmwall_hygienic|M-Wall Hygienic\nmwall_protect|M-Wall Protect\nmwall_design|M-Wall Design","required":"0","pick_format_type":"single","pick_format_single":"dropdown","pick_format_multi":"checkbox","pick_display_format_multi":"default","pick_display_format_separator":", ","pick_allow_add_new":"1","pick_taggable":"0","pick_show_icon":"1","pick_show_edit_link":"1","pick_show_view_link":"1","pick_limit":"0","pick_user_role":[],"pick_post_status":["publish"],"admin_only":"0","restrict_role":"0","restrict_capability":"0","hidden":"0","read_only":"0","roles_allowed":["administrator"],"rest_read":"0","rest_write":"0","rest_pick_response":"array","rest_pick_depth":"2","unique":"0","pick_select_text":"","pick_table_id":"","pick_table_index":"","pick_display":"","pick_where":"","pick_orderby":"","pick_groupby":""},"soort_informatie":{"id":531,"name":"soort_informatie","label":"Soort informatie","description":"","help":"","class":"","type":"pick","weight":2,"pick_object":"custom-simple","pick_val":"","sister_id":"","pick_custom":"leaflet|Leaflet\nbestektekst|Bestektekst\nkleurenoverzicht|Kleurenoverzicht\nbim_model|BIM model","required":"0","pick_format_type":"single","pick_format_single":"dropdown","pick_format_multi":"checkbox","pick_display_format_multi":"default","pick_display_format_separator":", ","pick_allow_add_new":"1","pick_taggable":"0","pick_show_icon":"1","pick_show_edit_link":"1","pick_show_view_link":"1","pick_limit":"0","pick_user_role":[],"pick_post_status":["publish"],"admin_only":"0","restrict_role":"0","restrict_capability":"0","hidden":"0","read_only":"0","roles_allowed":["administrator"],"rest_read":"0","rest_write":"0","rest_pick_response":"array","rest_pick_depth":"2","unique":"0","pick_select_text":"","pick_table_id":"","pick_table_index":"","pick_display":"","pick_where":"","pick_orderby":"","pick_groupby":""}},"show_in_menu":"1","label_singular":"Download","public":"0","show_ui":"1","supports_title":"1","supports_editor":"1","publicly_queryable":"0","exclude_from_search":"0","capability_type":"post","capability_type_custom":"download","capability_type_extra":"1","has_archive":"0","hierarchical":"0","rewrite":"1","rewrite_with_front":"1","rewrite_feeds":"0","rewrite_pages":"1","query_var":"1","can_export":"1","default_status":"draft","supports_author":"0","supports_thumbnail":"0","supports_excerpt":"0","supports_trackbacks":"0","supports_custom_fields":"0","supports_comments":"0","supports_revisions":"0","supports_page_attributes":"0","supports_post_formats":"0","built_in_taxonomies_category":"1","built_in_taxonomies_link_category":"0","built_in_taxonomies_post_tag":"0","built_in_taxonomies_product_cat":"0","built_in_taxonomies_product_shipping_class":"0","built_in_taxonomies_product_tag":"0","built_in_taxonomies_product_type":"0","built_in_taxonomies_product_visibility":"0","menu_position":"0","show_in_nav_menus":"1","show_in_admin_bar":"1","pfat_enable":"0","pfat_append_single":"append","pfat_filter_single":"the_content","pfat_append_archive":"append","pfat_filter_archive":"the_excerpt","pfat_run_outside_loop":"0","rest_enable":"0","read_all":"0","write_all":"0","built_in_taxonomies_pa_kleur":"0","built_in_taxonomies_translation_priority":"0","_wpml_location_migration_done":"1"}}}

[sc0ttkclark]

Looking into this issue this week and will review to see if I can reproduce the exact case where this error gets shown.

Please also consider testing on Pods 2.8.4 that just went out today and may resolve related issues.

[sc0ttkclark]

Can you go to Pods Admin > Edit Pods > Edit the "Downloads" pod > and then check the REST API tab. Ensure that the REST API is enabled for that post type. Something may have briefly caused it to become disabled perhaps?

[adcommunicatie]

Hello Scott,

I was off for a few weeks. Will come back to you soon.

[adcommunicatie]

The SQL-error problem is still there, also after updating to the latest version:

Database Error; SQL: SELECT DISTINCTt.* FROMwp_postsAStLEFT JOINwp_postmetaASdateONdate.meta_key= 'post_date' ANDdate.post_id=t.IDLEFT JOINwp_icl_translationsASwpml_translationsONwpml_translations.element_id=t.IDANDwpml_translations.element_type= 'post_download' ANDwpml_translations.language_code= 'nl' LEFT JOINwp_icl_languagesASwpml_languagesONwpml_languages.code=wpml_translations.language_codeANDwpml_languages.active= 1 WHERE ( (t.post_statusIN ( 'publish' ) ) AND (t.post_type= 'download' ) AND (wpml_languages.codeIS NOT NULL ) ) AND (t.product_filter= 'mwall_basic' ORt.product_filterLIKE '%"mwall\_basic"%' ) ORDER BYdate.meta_valueDESC,t.menu_order,t.post_title,t.post_date; Response: Unknown column 't.product_filter' in 'where clause'

My code to show filters:

filters( array( 'fields' => array( 'product_filter', 'soort_informatie' ), 'label' => 'Filteren' ) ); ?>

Showing results:

while ($get_downloads->fetch()) {
$get_title = $get_downloads->field('post_title');
echo $get_title;
}

[sc0ttkclark]

This appears fixed in Pods 2.8.9 now after work on 7884253

[adcommunicatie]

Hi Scott, thank you. We are still encountering SQL-errors in combination with WPML.

[JoryHogeveen]

Hi @adcommunicatie
Do you mean the errors only show when WPML is enabled?

[adcommunicatie]

Yes, that's correct. I did not test if it works when WPML is disabled. I don't have an available test environment.

[adcommunicatie]

Still not working