Events by Country and Category

[Quentin]

I am looking for a much more lightweight ability to filter events without utilizing the complexity of the Filter Bar add-on because we only need to be able to filter by two values, and want to create direct links for doing so. Basically, we want to be able to show localized category views by country without duplicating a bunch of categories unnecessarily.

i.e. show all events that belong to event-category-one and the Venue is in Canada. In a perfect world, accessible by a simple URL like mystic.com/events/category/category-one/canada

Are there any filters or arguments available in any of the venue or category Tribe__Events__Query queries available to limit in this manner?

[Brian]

Hi,

Thanks for using our plugins.

I can try to help out here, but limited in support existing features only.

Since the Event Category is a WordPress Taxonomy there are WordPress functions to use with that to generate list of events by category.

There is not feature in our plugin to filter by a Country. So I do not have anything to provide there.

However, with the latest version of the Filter Bar you could filter by a Pro additional field. So you could disable all but the category and a country additional field to filter by those two.

Otherwise, I do not have much else for you.

Let me know if you have any follow up questions.

Thanks

[Quentin]

Hi Brian, thanks for the response.

I’ve tried doing a pre_get_posts modification and custom meta_query, using a custom query_var we have registered in our theme.

So, a category url (domain.com/events/category/category-name/) can have a new query_var attached (?class_location=mexico) and then the meta_query is adjusted as follows:

/*
 * Set filters for Country Taxonomy
 */
$country_filter = get_query_var('class_location', false);

if ($country_filter && $country_filter != '') {
	
	$country_name = ucwords( str_replace('-', ' ', $country_filter) );
	$venue_args = array(
		'post_type' => 'tribe_venue',
		'meta_key' => '_VenueCountry',
		'meta_value' => $country_name,
		'fields' => 'ids',
		'orderby' => 'ID',
		'order' => 'ASC',
		'posts_per_page' => -1
	);

	$venue_ids = get_posts($venue_args);

	$meta_query = array(
		'relation' => 'OR',
		array(
			'key' => '_EventVenueID',
			'value' => $venue_ids,
			'compare' => 'IN',
		)
	);
	
	$query->set( 'meta_query', $meta_query );
}			
			

When I run the appropriate SQL that this query results in, I do receive the appropriate events matching the query, but when I run this on an events page, I receive the following error:

Unknown column ‘EventStartDate’ in ‘order clause’

On a category view without the custom meta_query added, the EventStartDate is never used in the SQL query. I have compared the WP_Query passed with and without the query_var added, and they are identical, except for the meta_query addition. But the SQL queries differ.

Without query_var:

SELECT SQL_CALC_FOUND_ROWS DISTINCT mat_posts.*, MIN(mat_postmeta.meta_value) as EventStartDate, MIN(tribe_event_end_date.meta_value) as EventEndDate FROM mat_posts INNER JOIN mat_term_relationships ON (mat_posts.ID = mat_term_relationships.object_id) INNER JOIN mat_term_relationships AS tt1 ON (mat_posts.ID = tt1.object_id) INNER JOIN mat_postmeta ON ( mat_posts.ID = mat_postmeta.post_id ) LEFT JOIN mat_postmeta as tribe_event_end_date ON ( mat_posts.ID = tribe_event_end_date.post_id AND tribe_event_end_date.meta_key = '_EventEndDate' ) WHERE 1=1 AND mat_posts.ID NOT IN (203,9268,9270,9272,9274,10526,11124,12148,12637,12980,14692,15666,15671,15618,15973,16023,12388,16370,16604,16607) AND ( mat_term_relationships.term_taxonomy_id IN (6) AND tt1.term_taxonomy_id IN (6) ) AND ( mat_postmeta.meta_key = '_EventStartDate' ) AND mat_posts.post_type = 'tribe_events' AND (mat_posts.post_status = 'publish' OR mat_posts.post_status = 'private') AND (mat_postmeta.meta_value >= '2016-01-08 12:26:23' OR (mat_postmeta.meta_value <= '2016-01-08 12:26:23' AND tribe_event_end_date.meta_value >= '2016-01-08 12:26:23' )) GROUP BY mat_posts.ID ORDER BY EventStartDate ASC, mat_posts.post_date ASC LIMIT 0, 10

With query_var:
SELECT SQL_CALC_FOUND_ROWS mat_posts.ID FROM mat_posts INNER JOIN mat_term_relationships ON (mat_posts.ID = mat_term_relationships.object_id) INNER JOIN mat_postmeta ON ( mat_posts.ID = mat_postmeta.post_id ) WHERE 1=1 AND ( mat_term_relationships.term_taxonomy_id IN (6) ) AND ( ( mat_postmeta.meta_key = '_EventVenueID' AND CAST(mat_postmeta.meta_value AS CHAR) IN ('12972','15992') ) ) AND mat_posts.post_type = 'tribe_events' AND (mat_posts.post_status = 'publish' OR mat_posts.post_status = 'private') GROUP BY mat_posts.ID ORDER BY EventStartDate DESC, mat_posts.post_date DESC LIMIT 0, 20

You’ll see in the default Query that there is a definition of the EventStartDate field, but not in the second, and within the passed WP_Query, the meta_query is the the only thing that has changed. I’m wondering if this is something that you have an action or filter linked to that we can leverage to get the correct SQL query passed as necessary in order to be able to make custom meta_query additions to the WP_Query on event pages.

I have dug around a bit in the source code of the plugin, and have, I believe, determined where these are being added in the SQL Query (Tribe__Events__Query line 406) but I want to make sure that this is the appropriate filter (tribe_events_query_posts_fields) to utilize to try and get this meta_query injected as necessary.

[Quentin]

Actually, after a bit more digging in that same query. I was able to simply change the priority of our pre_get_posts action to add our meta_query after your built-in pre_get_posts filter (priority 50) and after adding back in some date and order settings to the query seems to have solved our issue.

Thanks.

[Brian]

I am glad to see you were able to figure it out.

I am going to go ahead and close this ticket. If you have a similar issue or another in the future, please do not hesitate to create a new ticket.

Thanks!

[Author]

Posts