I have a created a custom table in my WP database called wp_opportunities which stores locations with their lat and long. I’m trying to query the table based on the taxonomy term set on the post ID column found in the opportunities table.
The taxonomies are called opportunity_type and opportunity_sector and I need it to only show me opportunities which are from specific opportunity_type, in this case ‘work-experience-year-10-12’ but also only show posts the posts if with they have the opportunity_sector ‘army’ included too.
The following code works if i switch the AND with OR in the t.slug =’army’ AND t.slug = ‘work-experience-year-10-12 but for some reason i cant get it to return when both are set.
SELECT * FROM wp_posts INNER JOIN wp_opportunities AS opportunities ON wp_posts.ID = opportunities.post_id LEFT JOIN wp_term_relationships AS tr ON opportunities.post_id = tr.object_id LEFT JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN wp_terms AS t ON t.term_id = tt.term_id WHERE wp_posts.post_type = 'opportunity' AND wp_posts.post_status = 'publish' AND ((t.slug ='army' AND t.slug = 'work-experience-year-10-12'))
Essentially, i’m trying to replicate the following:
'tax_query' => [ 'relation' => 'AND, [ 'taxonomy' => 'opportunity_sector', 'field' => 'slug', 'terms' => 'work-experience-year-10-12', ], [ 'taxonomy' => 'opportunity_type', 'field' => 'slug', 'terms' => 'army', ] ];
The reason i chose a custom table is because i need to also query the distance between each posts lat and long values and ACF currently stores the location meta in the postmeta table which as i understand is less efficient compared to doing it directly from its own table.
Can anyone help please?