Open eleektro opened 5 years ago
This was discussed on Slack Chat. The individual clauses in the WHERE work properly, it's only when they are concatenated with an AND that they break and it seems like the issue is specifically inside the where clause where it's adding in our necessary AND clauses (post_type and post_status)
Description
I have two pods (custom post type), one is called 'nuestros gatos' ('our_cats' in english) and the other is 'acogida_form' ('host_form').
'acogida_form' is related to 'nuestros_gatos' by a field called 'nombre_felino' ('cat_name' in english). Also, 'nuestros_gatos' has several fields, two of them are 'estado' ('status') and 'controlar_solicitudes' ('request_control'). Both are a simple relationship field, a custom list field.
On frontend, I present to the user (using pods->form()) a form with the 'acogida_form' pod. I need to filter those cats with this criteria:
estado.meta_value IN ('en_perrera', 'en_felinos', 'en_acogida') AND controlar_solicitudes.meta_value LIKE 'acogible'
To accomplish that, I am trying to set a custom WHERE clause on 'acogida_form' field 'nombre_felino'. If I use the first phrase
estado.meta_value IN ('en_perrera', 'en_felinos', 'en_acogida')
, it works fine. Same If I use the second phrasecontrolar_solicitudes.meta_value LIKE 'acogible'
, it works fine too.When using both, wordpress returns a 'database error':
Database Error; SQL: SELECT DISTINCT `t`.`ID`, `t`.`post_title`, `t`.`post_type` FROM `wp_posts` AS `t` LEFT JOIN `wp_postmeta` AS `estado` ON `estado`.`meta_key` = 'estado' AND `estado`.`post_id` = `t`.`ID` LEFT JOIN `wp_postmeta` AS `controlar_solicitudes_meta_value` ON `controlar_solicitudes_meta_value`.`meta_key` = 'meta_value' AND `controlar_solicitudes_meta_value`.`post_id` = `controlar_solicitudes`.`post_id` LEFT JOIN `wp_postmeta` AS `controlar_solicitudes` ON `controlar_solicitudes`.`meta_key` = 'controlar_solicitudes' AND `controlar_solicitudes`.`post_id` = `t`.`ID` WHERE ( ( `estado`.`meta_value` IN ('en_felinos', 'en_acogida', 'en_perrera') AND `controlar_solicitudes`.`meta_value` = 'acogible' ) AND ( `t`.`post_type` = "gatos" ) AND ( `t`.`post_status` IN ( "publish" ) ) ) ORDER BY `t`.`menu_order`, `t`.`post_title`, `t`.`post_date` LIMIT 0, 30; Response: Unknown column 'controlar_solicitudes.post_id' in 'on clause'
Steps To Reproduce
Steps to reproduce the behavior:
estado.meta_value IN ('en_felinos', 'en_acogida', 'en_perrera') AND controlar_solicitudes.meta_value LIKE 'acogible'
Expected behavior
A filtered list of 'nuestros_gatos' (our_cats)
Pods Version
2.7.12
WordPress Environment
Pods Package Export (helpful!)