Support

Home Forums Event Espresso Premium Problem querying on multiple meta_keys attached to an Event

Problem querying on multiple meta_keys attached to an Event

Posted: January 16, 2023 at 8:42 am


Gorkem

January 16, 2023 at 8:42 am

Hi there,

I’m using EE 4 and have an issue with running a query that checks multiple different meta_key / meta_value pairs on an Event post object.

I am trying to query specifically for Tickets, but need to take into account some ACF fields on the related Event.

Here is a basic example of what I’m trying to achieve:


EEM_Ticket::instance()->get_all([
[
// removed other query params for clarity
['status']["AND"] = [
'Datetime.Event.Post_Meta.meta_key' => 'level',
'Datetime.Event.Post_Meta.meta_value' => 'gcse',
],
['season']["AND"] = [
'Datetime.Event.Post_Meta.meta_key' => 'season',
'Datetime.Event.Post_Meta.meta_value' => 'easter',
];
]
]);

However, the SQL that this produces only uses one JOIN on the postmeta table:


SELECT
-- removed some fields for clarity
Datetime__Event___Post_Meta.meta_key AS 'Datetime__Event___Post_Meta.meta_key',
Datetime__Event___Post_Meta.meta_value AS 'Datetime__Event___Post_Meta.meta_value'
FROM
wp_esp_ticket AS Ticket
-- removed some joins for clarity
LEFT JOIN wp_postmeta AS Datetime__Event___Post_Meta ON Datetime__Event___Post_Meta.post_id = Datetime___Event_CPT.ID
WHERE
Ticket.TKT_deleted = 0
-- removed some where clauses for clarity
AND (
(
Datetime__Event___Post_Meta.meta_key = 'level'
AND Datetime__Event___Post_Meta.meta_value = 'gcse'
)
)
AND (
(
Datetime__Event___Post_Meta.meta_key = 'season'
AND Datetime__Event___Post_Meta.meta_value = easter'
)
)

This always returns 0 results because the single postmeta JOIN means that the resulting JOIN’d row only contains the postmeta where meta_key = ‘level’, which means that the second AND (meta_key = ‘season’ + meta_value = easter’) will never match.

When you manually create a similar query with WP_Query and the meta_query option (note: I’m querying Events here instead of Tickets to removed the complicated relationships):


$query = new WP_Query([
'post_type' => 'espresso_events',
'meta_query' => [
[
'key' => 'level',
'value' => ['gcse'],
'compare' => 'IN',
],
[
'key' => 'season',
'value' => ['easter'],
'compare' => 'IN',
],
],
]);

You get the following SQL with multiple postmeta JOINs:


SELECT
SQL_CALC_FOUND_ROWS wp_posts.ID
FROM
wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE
1 = 1
AND (
(
wp_postmeta.meta_key = 'level'
AND wp_postmeta.meta_value IN ('gcse')
)
AND (
mt1.meta_key = 'season'
AND mt1.meta_value IN ('easter')
)
)
AND wp_posts.post_type = 'espresso_events'
GROUP BY
wp_posts.ID
ORDER BY
wp_posts.post_date DESC

Because postmeta has been included twice, the query can correctly include both the ‘level’ meta_key + meta_value and the ‘season’ meta_key + meta_value and check them separately.

I’ve combed through the docs and even tried to parse the EE SQL creation code but I can’t seem to find a fix for this.

I also tried to search the support forums but only found one unanswered post from 2016: https://eventespresso.com/topic/filter-events-with-multiple-custom-meta-keys-and-value/

Does anyone know if this possible to do within the current EE functionality, or will I need to come up with a workaround?

Thanks in advance!


Gorkem

January 16, 2023 at 8:55 am

Sorry for the messy formatting of the code above – here are some screenshots if that’s easier to read:

Code#1
Code#2
Code#3
Code#4


Gorkem

January 16, 2023 at 8:59 am

Corrected images!

Code#1
Code#1
Code#1
Code#1

Apologies if I’m being blind and missing the preview / edit buttons for posts somewhere!


Tony

  • Support Staff

January 18, 2023 at 4:52 am

Hi there,

Firstly thank you for the detailed report, it really does help narrow down to see the issue at hand.

Having said that I’d recommend opening up an issue on the repo for this HERE. Our developers do not monitor the support forums and they are the best people to answer this for you.

I would basically just be relaying the info to and from the support forums for this so you may as well skip that for the above.


Gorkem

January 22, 2023 at 4:43 pm

Thanks Tony!

Apologies for the late reply – we got to work on a potential alternative in the meantime so we were heads down on that.

Unfortunately, the alternative was to try to use a new custom category / taxonomy instead, but that had the same issue with cancelling itself out when also querying on the EE category.

I’ll write up an issue on the repo now.

Thanks again!


Gorkem

January 22, 2023 at 5:20 pm

For any future readers that might be affected by this issue, the Github issue is here: https://github.com/eventespresso/event-espresso-core/issues/4121

You must be logged in to reply to this support post. Sign In or Register for an Account

Event Espresso