I use the following SQL query to fetch custom posts in Wordpress. I ended up with this solution because i needed to fetch und sort the posts based on custom meta data. This woks pretty well so far!
But how do i incorporate custom taxonomies in my query? Say for example only fetch posts that are associated with term_id 5? I'm really stuck here cause i can't figure out how wp_terms, relations etc are connected...
Any help is greatly appreciated!
UPDATE: After some reading the solution in my case is this:
WordPress stores the relations between taxonomies and posts in wp_term_relationships (where object_id is the post_id and term_taxonomy_id ist the term_id in wp_terms). So if i want to only fetch posts that belong to a specific term_id, i came up with the following query. Seems to work as far as i can tell!
This is my query
global $wpdb;
$activelang = ICL_LANGUAGE_CODE;
$cmonth = date('Y-m');
$myquery = "
SELECT wposts.*, wpostmeta.meta_value AS date
FROM wp_posts wposts, wp_postmeta wpostmeta, wp_icl_translations wicl_translations, , wp_term_relationships wptermrelations
WHERE wposts.post_status = 'publish'
AND wpostmeta.post_id = wposts.ID
AND (wptermrelations.object_id = wposts.ID AND (wptermrelations.term_taxonomy_id = '29' OR wptermrelations.term_taxonomy_id = '30'))
AND wposts.post_type = 'event'
AND wicl_translations.element_id = wposts.ID
AND wicl_translations.language_code = '$activelang'
AND (wpostmeta.meta_key = '_ws_prem_date' OR wpostmeta.meta_key = '_ws_date1_date' OR ... ... OR wpostmeta.meta_key = '_ws_date10_date')
AND wpostmeta.meta_value >= '$cmonth-01'
ORDER BY CAST(date AS DATETIME),wposts.post_title ASC
";
$myloop = $wpdb->get_results($myquery);
In case someone is wondering what this query does:
This query fetches custom post types (events that have multiple dates associated) and builds a loop for these events that allows for duplicate entries since a 'normal' WordPress loop always filters out any duplicate entries.
