We have 3 custom post types and we need to query all 3 and join them in order the get that data to build up a complete list of films at a venue.
Tables are as follows, with there unique ID's. The unique ID's are stored inside a custom field.
- Cinema Venues (venues) - venueID
- Cinema Events (events) - venueID & filmID
- Cinema Films (films) - filmID
NB: Cinema Events contains all the pricing information + times for each film.
How can I join these with a statement to obtain all events + films at a venue?
With a query simalar to this.
$filmsAtCinema = get_posts(array( 'post_type' => array('venues','events','films') 'meta_query' => array( 'relation' => 'and', array( 'key' => 'venues', 'value' => 'venueID', 'compare' => 'LIKE' ), array( 'key' => 'events', 'value' => 'venueID', 'compare' => 'LIKE' ), array( 'key' => 'films', 'value' => 'filmID', // Film ID obtained from Events. 'compare' => 'LIKE' ) ) ));
Any help is greatly appreciated. Thanks!