I used below code to select products from specific categories and works fine but I want to select products if include more than one category
SELECT post.ID, post.post_title FROM `wp_posts` as post
INNER JOIN wp_term_relationships AS tr ON tr.object_id = post.ID
WHERE
post.`post_type` IN ('product','product_variation')
AND tr.term_taxonomy_id IN(32,25)
I use IN(32,25) and it returns all products, how can I filter products just included in two categories?
To query products that are in specific categories (e.g. categories with the ids of 32 and 35), you could use this:
SELECT wp_posts.* FROM wp_posts LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND
( wp_term_relationships.term_taxonomy_id IN (32,35) )
AND
wp_posts.post_type = 'product'
AND
(wp_posts.post_status = 'publish')
GROUP BY
wp_posts.ID
ORDER BY
wp_posts.post_date DESC
It's recommended to use global $wpdb and take advantage of
$wpdb->prefix for your wordpress table "prefix", instead if hard coding "wp_"and
$wpdb->prepare for security.Like this:
global $wpdb;
$query = $wpdb->prepare(
"SELECT {$wpdb->prefix}posts.* FROM {$wpdb->prefix}posts LEFT JOIN {$wpdb->prefix}term_relationships
ON ({$wpdb->prefix}posts.ID = {$wpdb->prefix}term_relationships.object_id)
WHERE 1=1
AND
( {$wpdb->prefix}term_relationships.term_taxonomy_id IN (32,35) )
AND
{$wpdb->prefix}posts.post_type = 'product'
AND
({$wpdb->prefix}posts.post_status = 'publish')
GROUP BY
{$wpdb->prefix}posts.ID
ORDER BY
{$wpdb->prefix}posts.post_date DESC"
);
$sql_results = $wpdb->get_results($query, ARRAY_A);
For security reasons, avoid writing your own sql queries as much as possible.
In order to query your database try to use:
or
{$wpdb->prefix}term_relationships.term_taxonomy_id IN (32,35) all product include in this two categories listed. I just want to return the existing products in both categories. in some case maybe just include in one of them
GROUP BY 1,2 HAVING COUNT(DISTINCT tr.term_taxonomy_id) = 2