Support

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!