Relationship Autocomplete Issue error on Customized Order By Configuration

[slyderza]

Describe the bug I have a field on a Groups Custom Post Type that is set to be a Relation to a Products Custom Post Type. When choosing Autocomplete as the Format and a Meta Value as the Display Field I get a SQL error when searching on that field.

If I set the field to format Checkboxes then it shows the Meta Values fine. The SQL Error only happens when the Autocomplete Ajax request happens.

To Reproduce See below 2 screenshots showing how I have set up the relation field.

When searching here is the error: error.txt

Expected behavior I expect a list of product rows based on the post_title_pretty meta value.

WordPress Environment

``` WordPress Version: 4.9.6 PHP Version: 7.0.21 MySQL Version: 5.6.36 Server Software: Apache Your User Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36 Session Save Path: /tmp Session Save Path Exists: Yes Session Save Path Writeable: Yes Session Max Lifetime: 1440 Opcode Cache: Apc: No Memcached: No OPcache: Yes Redis: No Object Cache: APC: No APCu: No Memcache: No Memcached: No Redis: No WPDB Prefix: wp_ WP Multisite Mode: No WP Memory Limit: 40M Pods Network-Wide Activated: No Pods Install Location: /home/pelican/pelican.co.za/www/wp-content/plugins/pods/ Pods Tableless Mode Activated: No Pods Light Mode Activated: No Currently Active Theme: Whitehart Currently Active Plugins: Admin Columns Pro: 4.2.3 Akismet Anti-Spam: 4.0.8 AMP: 0.7.2 Category Order and Taxonomy Terms Order: 1.5.4 Contact Form 7: 5.0.2 Contact Form 7 - Dynamic Text Extension: 2.0.2.1 Contact Form 7 Honeypot: 1.13 Custom Taxonomy Order NE: 2.9.3 Easy WP SMTP: 1.3.6 Flamingo: 1.8 Image Watermark: 1.6.4 Justified Image Grid: 3.9 ManageWP - Worker: 4.5.0 Media File Renamer: 4.2.1 Pods - Custom Content Types and Fields: 2.7.6 Post Types Order: 1.9.3.6 Powerful Posts Per Page: 1.0.0 Regenerate Thumbnails: 3.0.2 Responsive Menu: 3.1.14 Safe Redirect Manager: 1.9 Set email sender: 0.1 Slider Revolution: 5.4.5.1 User Role Editor: 4.43 Visual Table Formatting: 1.8.8 WooCommerce: 3.4.3 WPBakery Page Builder: 5.5 WP Sitemap Page: 1.6.1 Yoast SEO: 7.7.1 ```

Pods Package Export (helpful!)

[package export.txt](https://github.com/pods-framework/pods/files/2149448/package.export.txt)

[jimtrue]

@slyderza If you can, please provide us the Pods Migrate:Packages export as requested in the Bug Report Template (Pods Admin, Components, Migrate:Packages, Enable, then Migrate: Packages, Export and save the result as a text file and upload here).

It speeds up our testing of your bug and verifies that we are configuring our test identically. Thanks!

[slyderza]

Here you go:

package export.txt

[jimtrue]

Thanks! @slyderza :)

[jimtrue]

Mine actually worked immediately. @slyderza Have you tried duplicating this to a staging site and deactivating all your plugins except for WooCommerce and Pods?

This might actually be a plugin conflict.

[jimtrue]

Granted I also find that SQL Error unusual:

<e>Database Error; SQL: SELECT

DISTINCT
`t`.`ID`, `post_title_pretty`.`meta_value` AS post_title_pretty, `t`.`post_type`
FROM `wp_posts` AS `t`

    LEFT JOIN `wp_postmeta` AS `post_title_pretty` ON
        `post_title_pretty`.`meta_key` = 'post_title_pretty'
        AND `post_title_pretty`.`post_id` = `t`.`ID`

WHERE ( ( `t`.`post_status` IN ( "publish" ) ) AND ( `t`.`post_title_pretty` LIKE '%e%' OR `t`.`post_title` LIKE '%e%' OR `t`.`post_name` LIKE '%e%' OR `t`.`post_content` LIKE '%e%' OR `t`.`post_excerpt` LIKE '%e%' ) AND ( `t`.`post_type` = "product" ) )

ORDER BY (`t`.`post_title_pretty` LIKE '%e%' ) DESC, `post_title_pretty`.`meta_value`, `t`.`post_title_pretty`, `t`.`ID`, `t`.`menu_order`, `t`.`post_title`, `t`.`post_date`
LIMIT 0, 15; Response: Unknown column 't.post_title_pretty' in 'where clause'

[slyderza]

@jimtrue Your screenshot shows Checkboxes not the Autocomplete, Checkboxes work fine for me too.

I have set up a blank copy of Wordpress with just Pods, Woocommerce and Fakerpress and am getting the same error.

Here is my findings:

1. You need to have more than 2-3 posts otherwise the Autocomplete doesn't use Ajax but just shows the list straight away which shows the meta value properly hence I used Fakerpress to generate some.
2. The Autocomplete works perfectly when just using default settings, the issue comes in as soon as I put in {@meta name} for the Display so that a custom meta value is shown.

I have a test site with the issue happening, can I invite you to the site for testing? You can then see the error happening and also play with my settings.

[jimtrue]

@slyderza My apologies, I imported your package as provided which imported them all as checkboxes (which is probably your current configuration) so I didn't go back in to change them.

After switching (I did both AutoComplete for 1 and List View for the 2nd, leaving the remainders in Calculator Group as Checkbox) I didn't get the error with AutoComplete or Listview (which both use the Select2 AJAX searching), until I went past about 20 entries. I never get the SQL error on my installation (which only has WooCommerce, Pods and FakerPress), but the Ajax Search in Select2 is not working (with no JS errors in the Dev Console). If I remove your custom Orderby and override for the Field to display in Select, they both work.

If you can, send us an email to support@pods.io with login/invite to the site for testing. @pglewis This looks like we do have an issue here, but need to see the replicated SQL error, too. I'm also going to try to create a more simplified test for it.

[slyderza]

I have created a user and sent details through to support@pods.io

Thank you.

[jimtrue]

@slyderza Nothing populated through here yet, unfortunately. I sent a test email after just to make sure our HelpScout wasn't stuck ;)

[slyderza]

Apologies I don't believe my server is sending emails, I have manually mailed through the login details.

[jimtrue]

@slyderza Thanks for the website access. Yep, you're getting the same issue I was seeing locally. The AJAX just comes back with no response to the search. Where were you getting the SQL error you included above?

[slyderza]

@jimtrue If you open Developer Tools and check Network activity before doing a search when you do the search you will see the Ajax calls response.

[jimtrue]

Thanks for that additional feedback @slyderza! Yes, I can replicate this and it indicates a two-fold issue:

1) Obviously we've got some odd construction happening on the SQL for something that should work normally (ie Customized Order By), and

2) Our DFV isn't providing any error reporting when it gets a SQL error.

[GeoffMartinSkyward]

I am also having a similar issue with our project. Long lists or relationship content that do not use the default "Title" field for the Display Field in Selection List provide no auto-complete content (with format 'Autocomplete' or 'List view').