Hello,
I have two custom meta keys (type & genre). I’m using EE4 model querying
to filter events, but only one is being used and the other is ignored. After inspecting the SQL generated, it seems that EE_Base does not automatically generate a SELF-JOIN, which how such case should be handled. Is there any native way of doing this in EE4?
$events = EEM_Event::instance()->get_all(array(
array(
‘AND’ => array(
‘Post_Meta.meta_value*a’=>’genre’,
‘Post_Meta.meta_value*b’=>’genre1’,
),
‘AND’ => array(
‘Post_Meta.meta_key*c’=>’type’,
‘Post_Meta.meta_value*d’=>’type1’,
)
)));
I tried using the “on_join_limit” but it doesn’t work, and I believe it won’t for two reasons. First, I need to do self join on wp_postmeta in this case there has to be two aliases Post_Meta1 and Post_Meta2 for it to work. Can you please help as this is urgent?
I used EE4 Extra Meta to save the extra fields as per your suggestions which took considerably alot of time to update. Yet still i’m facing the same problem with joins, KINDLY give this issue priority and try to understand the problem before suggesting irrelevant solutions.
I tried the mentioned solution by using the Extra Meta but it didnt work simply because I’m trying to self join with the same table to filter Events using multiple (post meta keys and values) at the same time.
If i use WP_QUERY I would lose the ability to filter with venues since they are not attached to the default wordpress post.
So the only way to be able to filter is using the model system, now it works for all but for this exception case of trying to filter events by two custom keys using an “AND” operator doesn’t work.
After reading the documentation for ‘on_join_limit’ it seems that it only works for Primary Table join in my case. The query is executed on the Events model and I want to do a self join on another table.
Is there a way that maybe I can create a custom SQL Query and somehow have this attached to an EE_Event object ?
Thank you so much for you reply, I appreciate your help.
However I got the below error "Unknown column 'Post_Meta.ID' in 'on clause'" while running wpdb method "get_results"
you can check the SQL Query generated
SELECT Event_CPT.ID AS 'Event_CPT.ID', Event_CPT.post_title AS 'Event_CPT.post_title', Event_CPT.post_content AS 'Event_CPT.post_content', Event_CPT.post_name AS 'Event_CPT.post_name', Event_CPT.post_date AS 'Event_CPT.post_date', Event_CPT.post_excerpt AS 'Event_CPT.post_excerpt', Event_CPT.post_modified AS 'Event_CPT.post_modified', Event_CPT.post_author AS 'Event_CPT.post_author', Event_CPT.post_parent AS 'Event_CPT.post_parent', Event_CPT.menu_order AS 'Event_CPT.menu_order', Event_CPT.post_status AS 'Event_CPT.post_status', Event_CPT.comment_status AS 'Event_CPT.comment_status', Event_CPT.ping_status AS 'Event_CPT.ping_status', Event_Meta.EVT_display_desc AS 'Event_Meta.EVT_display_desc', Event_Meta.EVT_display_ticket_selector AS 'Event_Meta.EVT_display_ticket_selector', Event_Meta.EVT_visible_on AS 'Event_Meta.EVT_visible_on', Event_Meta.EVT_additional_limit AS 'Event_Meta.EVT_additional_limit', Event_Meta.EVT_default_registration_status AS 'Event_Meta.EVT_default_registration_status', Event_Meta.EVT_member_only AS 'Event_Meta.EVT_member_only', Event_Meta.EVT_phone AS 'Event_Meta.EVT_phone', Event_Meta.EVT_allow_overflow AS 'Event_Meta.EVT_allow_overflow', Event_Meta.EVT_timezone_string AS 'Event_Meta.EVT_timezone_string', Event_Meta.EVT_external_URL AS 'Event_Meta.EVT_external_URL', Event_Meta.EVT_donations AS 'Event_Meta.EVT_donations', Event_CPT.ID AS 'Event_CPT.ID', Event_Meta.EVTM_ID AS 'Event_Meta.EVTM_ID', Post_Meta.meta_id AS 'Post_Meta.meta_id', Post_Meta.post_id AS 'Post_Meta.post_id', Post_Meta.meta_key AS 'Post_Meta.meta_key', Post_Meta.meta_value AS 'Post_Meta.meta_value', Datetime.DTT_ID AS 'Datetime.DTT_ID', Datetime.EVT_ID AS 'Datetime.EVT_ID', Datetime.DTT_name AS 'Datetime.DTT_name', Datetime.DTT_description AS 'Datetime.DTT_description', Datetime.DTT_EVT_start AS 'Datetime.DTT_EVT_start', Datetime.DTT_EVT_end AS 'Datetime.DTT_EVT_end', Datetime.DTT_reg_limit AS 'Datetime.DTT_reg_limit', Datetime.DTT_sold AS 'Datetime.DTT_sold', Datetime.DTT_reserved AS 'Datetime.DTT_reserved', Datetime.DTT_is_primary AS 'Datetime.DTT_is_primary', Datetime.DTT_order AS 'Datetime.DTT_order', Datetime.DTT_parent AS 'Datetime.DTT_parent', Datetime.DTT_deleted AS 'Datetime.DTT_deleted' FROM wp_posts AS Event_CPT LEFT JOIN wp_esp_event_meta AS Event_Meta ON Post_Meta.ID = Event_Meta.EVT_ID LEFT JOIN wp_postmeta AS Post_Meta ON Post_Meta.post_id=Event_CPT.ID LEFT JOIN wp_esp_datetime AS Datetime ON Datetime.EVT_ID=Event_CPT.ID WHERE Event_CPT.post_type = 'espresso_events' AND Event_CPT.post_status NOT IN ('auto-draft','trash') AND ( (Datetime.DTT_deleted = 0) OR Datetime.DTT_ID IS NULL) AND Post_Meta.meta_key = 'genre' AND Post_Meta.meta_value = 'Singles' AND Post_Meta.meta_key = 'type' AND Post_Meta.meta_value = 'Free' GROUP BY Event_CPT.ID ORDER BY Datetime.DTT_EVT_start ASC LIMIT 0,3
I cropped only the part with the problem below 🙂 thanks
FROM wp_posts AS Event_CPT LEFT JOIN wp_esp_event_meta AS Event_Meta ON Post_Meta.ID = Event_Meta.EVT_ID LEFT JOIN wp_postmeta AS Post_Meta ON Post_Meta.post_id=Event_CPT.ID LEFT JOIN wp_esp_datetime AS Datetime ON Datetime.EVT_ID=Event_CPT.ID
The support post ‘EE4 Filter events with multiple custom meta keys and value’ is closed to new replies.
Have a question about this support post? Create a new support post in our support forums and include a link to this existing support post so we can help you.
Support forum for Event Espresso 3 and Event Espresso 4.