Custom $wpdb Posts filtered by Taxonomy Meta

Working on a complex set up where we have 2 separate WordPress installs on the same database with different prefixes.

I need to make some queries for products from Site A for Site B.

Gotten a little rusty with my SQL as I mainly use built in WP functions.

On site A I’m using get_terms using a meta query to loop through a taxonomy and then making another query on the posts for each.

On Site B I’d like to do 1 custom $wpdb query and have it almost working where I’m checking a post meta value and then ordering by the taxonomy name like so:

 $querystr = "
  SELECT $tsm_posts.*, $, $siteA_terms.slug, tax2.description
  FROM $siteA_posts, $siteA_postmeta
  INNER JOIN {$siteA_term_relationships} AS rel2 ON $siteA_postmeta.post_id = rel2.object_id
  INNER JOIN {$siteA_term_taxonomy} AS tax2 ON rel2.term_taxonomy_id = tax2.term_taxonomy_id
  INNER JOIN {$siteA_terms} USING (term_id)
  WHERE $siteA_posts.ID = $siteA_postmeta.post_id
  AND $siteA_postmeta.meta_key = 'siteB_product'
  AND $siteA_postmeta.meta_value = 'yes'
  AND $siteA_posts.post_status = 'publish'
  AND $siteA_posts.post_type = '" . SP_PRODUCTS . "'
  AND $siteA_posts.post_parent = '0'
  GROUP BY rel2.object_id
  ORDER BY GROUP_CONCAT({$siteA_terms}.name ORDER BY name ASC)";
  $siteA_products = $wpdb->get_results($querystr, OBJECT);

The one main issue I’m having is to also be able to filter by a taxonomy meta value as well as some categories are not meant to be displayed.

So I’ve been trying to do another INNER JOIN on the taxonomy meta table but I’m having issues and not getting the desired results:

  INNER JOIN {$siteA_termmeta} AS taxMeta ON ( taxMeta.term_id = rel2.term_taxonomy_id AND taxMeta.meta_key = 'product_category_main' AND taxMeta.meta_value = 'YES' )

Any suggestions greatly appreciated.

Read more here: Custom $wpdb Posts filtered by Taxonomy Meta

Leave a Reply

Your email address will not be published. Required fields are marked *