Custom $wpdb Posts filtered by Taxonomy Meta

Question

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.name, $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.

user892670 user892670 · Accepted Answer · 2017-05-17T17:50:53

So still having an issue with the taxonomy meta but I was able to achieve the results I was after by limiting the taxonomy itself and have cleaned up the code adding specific calls to needed post meta as well incase anyone finds it useful :)

So this code filters by post meta and taxonomy and then orders the posts by the taxonomy title.

Note: Knocking off the rust on my SQL it's much easier to name the specific calls to Joins and the extra meta etc to make it easier to extract and identify.

$query = "
SELECT p.ID, p.post_title, p.post_content,
fi_id.meta_value AS featured_id, taxable.meta_value AS taxable,
producer.name AS producer_name, producer.slug AS producer_slug, producer_tax.description AS producer_description
FROM {$siteA_posts} p
LEFT JOIN {$siteA_postmeta} fi_id ON fi_id.post_id = p.ID AND fi_id.meta_key = '_thumbnail_id'
LEFT JOIN {$siteA_postmeta} taxable ON taxable.post_id = p.ID AND taxable.meta_key = 'taxable'
LEFT JOIN {$siteA_postmeta} loc ON loc.post_id = p.ID AND loc.meta_key = 'location_loftbox'
INNER JOIN {$siteA_term_relationships} AS rel2 ON loc.post_id = rel2.object_id
INNER JOIN {$siteA_term_taxonomy} AS producer_tax ON rel2.term_taxonomy_id = producer_tax.term_taxonomy_id AND producer_tax.taxonomy = 'producers'
INNER JOIN {$siteA_terms} AS producer USING (term_id)
WHERE p.ID = loc.post_id
AND loc.meta_key = 'siteB_product'
AND loc.meta_value = 'yes'
AND p.post_status = 'publish'
AND p.post_type = 'products'
AND p.post_parent = '0'
GROUP BY rel2.object_id
ORDER BY GROUP_CONCAT(producer.name ORDER BY name ASC)
";

Custom $wpdb Posts filtered by Taxonomy Meta

1 Answers