Question 1838 in Wordpress Question posted in
The official Wordpress documentation can be found here.

Get woocommerce products by SQL that are front end visible (not private, not password protected, are able to be displayed in search or catalog) – WordPress

I’m running the following SQL code:

                    SELECT DISTINCT p.ID
                    FROM {$wpdb->prefix}posts AS p
                    INNER JOIN {$wpdb->prefix}term_relationships AS tr ON p.ID = tr.object_id
                    INNER JOIN {$wpdb->prefix}term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                    INNER JOIN {$wpdb->prefix}term_relationships AS tr2 ON p.ID = tr2.object_id
                    INNER JOIN {$wpdb->prefix}term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
                    INNER JOIN {$wpdb->prefix}postmeta AS pm ON p.ID = pm.post_id
                    WHERE p.post_type = 'product'
                    AND p.post_status = 'publish'
                    AND tt.taxonomy = 'product_cat'
                    AND tt.term_id IN (%s)
                    AND tt.parent != 0
                    AND tt2.taxonomy = 'product_visibility'
                    AND tt2.term_id NOT IN ('" . wc_get_product_visibility_term_ids() . "')
                    AND pm.meta_key = '_stock_status'
                    AND pm.meta_value = 'instock'
                    GROUP BY p.ID
                    ORDER BY SUM(pm.meta_value) DESC, RAND()
                    LIMIT %d

Whenever I remove this:

                    AND tt2.taxonomy = 'product_visibility'
                    AND tt2.term_id NOT IN ('" . wc_get_product_visibility_term_ids() . "')

Then the code returns products, but with it, it returns nothing (not even visible products). I want to only include products that should be visible on the front end in any way (not private, not password protected, are able to be displayed in search or catalog).

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I figured it out. Visible products don't always have a product_visibility entry. Therefore, I had to check if product_visibility was null OR if it did match, whether it was not a product_visibility ID that makes the product invisible. Here is the updated code:

        SELECT DISTINCT p.ID
                    FROM {$wpdb->prefix}posts AS p
                    INNER JOIN {$wpdb->prefix}term_relationships AS tr ON p.ID = tr.object_id
                    INNER JOIN {$wpdb->prefix}term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
                    LEFT JOIN (
                      SELECT tr2.object_id
                      FROM {$wpdb->prefix}term_relationships AS tr2
                      INNER JOIN {$wpdb->prefix}term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
                      WHERE tt2.taxonomy = 'product_visibility'
                      AND tt2.term_id IN (7, 6, 9, 10, 11, 12)
                    ) AS excluded_visibility ON p.ID = excluded_visibility.object_id
                    INNER JOIN {$wpdb->prefix}postmeta AS pm ON p.ID = pm.post_id
                    WHERE p.post_type = 'product'
                    AND p.post_status = 'publish'
                    AND tt.taxonomy = 'product_cat'
                    AND tt.term_id IN (%s)
                    AND tt.parent != 0
                    AND (excluded_visibility.object_id IS NULL OR tr.term_taxonomy_id NOT IN (excluded_visibility.object_id))
                    AND pm.meta_key = '_stock_status'
                    AND pm.meta_value = 'instock'
                    GROUP BY p.ID
                    ORDER BY SUM(pm.meta_value) DESC, RAND()
                    LIMIT %d

  2. 0

    if you are trying to filter the products based on their visibility using wc_get_product_visibility_term_ids(), it returns term IDs of the product visibility taxonomies. The problem may be that you are using the NOT IN which expects a list of values, not a string, so you may need to pass an array of values instead.

    SELECT DISTINCT p.ID
FROM {$wpdb->prefix}posts AS p
INNER JOIN {$wpdb->prefix}term_relationships AS tr ON p.ID = tr.object_id
INNER JOIN {$wpdb->prefix}term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
INNER JOIN {$wpdb->prefix}term_relationships AS tr2 ON p.ID = tr2.object_id
INNER JOIN {$wpdb->prefix}term_taxonomy AS tt2 ON tr2.term_taxonomy_id = tt2.term_taxonomy_id
INNER JOIN {$wpdb->prefix}postmeta AS pm ON p.ID = pm.post_id
WHERE p.post_type = 'product'
AND p.post_status = 'publish'
AND tt.taxonomy = 'product_cat'
AND tt.term_id IN (%s)
AND tt.parent != 0
AND tt2.taxonomy = 'product_visibility'
AND tt2.term_id NOT IN (" . implode(',', wc_get_product_visibility_term_ids()) . ")
AND pm.meta_key = '_stock_status'
AND pm.meta_value = 'instock'
GROUP BY p.ID
ORDER BY SUM(pm.meta_value) DESC, RAND()
LIMIT %d
    • replaced the "'" around wc_get_product_visibility_term_ids() with parentheses () + used implode() to convert the returned term IDs array into a comma-separated list of values(should correctly exclude the product visibility terms from the query and only return the visible products)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search