Tribe Events Global Nav SQL

Queries.sql

/*Gets all posts with type 'Tribe Events'*/
SELECT posts.*
FROM wp_posts AS posts
JOIN wp_postmeta AS pm1
    ON (posts.ID = pm1.post_id AND pm1.meta_key = '_EventStartDate')
JOIN wp_term_relationships AS term_relationships
    ON posts.ID = term_relationships.object_id
INNER JOIN wp_term_taxonomy AS term_taxonomy
    ON term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id
JOIN wp_terms AS terms
    ON term_taxonomy.term_id = terms.term_id
/*Where the location cookie will have to pass in the city name. We can also use terms.slug = los-angeles for example*/
WHERE terms.name = 'Los Angeles' 
AND posts.post_type = 'tribe_events'
/*In the original function, the date (being set as the current time here) is passed in from the current post. The greater than or less than operator is dynamically added too, depending on whether you are paging forward or back*/
AND ( pm1.meta_key = '_EventStartDate' AND CAST(pm1.meta_value AS SIGNED) > '".time()."')
LIMIT 1;  
/*The limit one is only for pagination purposes, I would take it off when testing the query.*/



/*Adding this cray to Wordpress v1.*/
            $eventsQuery = $wpdb->prepare("
                SELECT $wpdb->posts.*, d1.meta_value as EventStartDate
                FROM $wpdb->posts
                JOIN $wpdb->postmeta as d1 ON ($wpdb->posts.ID = d1.post_id)
                JOIN wp_term_relationships AS term_relationships ON ($wpdb->posts.ID = term_relationships.object_id)
                JOIN wp_term_taxonomy AS term_taxonomy ON (term_relationships.term_taxonomy_id = term_taxonomy.term_taxonomy_id)
                JOIN wp_terms AS terms ON (term_taxonomy.term_id = terms.term_id)
                WHERE $wpdb->posts.post_type = '%s'
                AND terms.name = '%s'
                AND d1.meta_key = '_EventStartDate'
                AND ((d1.meta_value = '%s' AND ID $sign %d) OR
                    d1.meta_value $sign '%s')
                AND $wpdb->posts.post_status = 'publish'
                AND ($wpdb->posts.ID != %d OR d1.meta_value != '%s')
                ORDER BY TIMESTAMP(d1.meta_value) $order, ID $order
                LIMIT 1", self::POSTTYPE, $loc, $date, $id, $date, $id, $date );