I have the following SQL code that works badly because when I try to filter by product category it displays empty records:

SQL code:

SELECT
    wp_posts.ID,
    CONCAT( '<a href="', wp_posts.guid, '">', wp_posts.post_title, '</a>' ) AS nazwa,
    product_taxonomy_product_cat_tbl.NAME AS kategoria,
    product_taxonomy_pa_j_m_tbl.NAME AS jednostka,
    product_quant.meta_value AS ilość,
    product_price.meta_value AS cena,
    product_weight.meta_value AS waga,
    product_sku.meta_value AS sku 
FROM
    wp_posts
    LEFT JOIN wp_postmeta product_quant ON product_quant.post_id = wp_posts.ID 
    AND product_quant.meta_key = "_stock"
    LEFT JOIN wp_postmeta product_price ON product_price.post_id = wp_posts.ID 
    AND product_price.meta_key = "_price"
    LEFT JOIN wp_postmeta product_weight ON product_weight.post_id = wp_posts.ID 
    AND product_weight.meta_key = "_weight"
    LEFT JOIN wp_postmeta product_sku ON product_sku.post_id = wp_posts.ID 
    AND product_sku.meta_key = "_sku"
    INNER JOIN (
    SELECT NAME
        ,
        object_id AS id 
    FROM
        wp_terms AS product_taxonomy_product_cat_tbl_terms
        INNER JOIN wp_term_taxonomy AS product_taxonomy_product_cat_tbl_termtaxonomy ON product_taxonomy_product_cat_tbl_termtaxonomy.term_id = product_taxonomy_product_cat_tbl_terms.term_id 
        AND product_taxonomy_product_cat_tbl_termtaxonomy.taxonomy = 'product_cat'
        INNER JOIN wp_term_relationships AS rel_product_taxonomy_product_cat_tbl ON product_taxonomy_product_cat_tbl_termtaxonomy.term_taxonomy_id = rel_product_taxonomy_product_cat_tbl.term_taxonomy_id 
    ) AS product_taxonomy_product_cat_tbl ON product_taxonomy_product_cat_tbl.ID = wp_posts.id
    INNER JOIN (
    SELECT NAME
        ,
        object_id AS id 
    FROM
        wp_terms AS product_taxonomy_pa_j_m_tbl_terms
        INNER JOIN wp_term_taxonomy AS product_taxonomy_pa_j_m_tbl_termtaxonomy ON product_taxonomy_pa_j_m_tbl_termtaxonomy.term_id = product_taxonomy_pa_j_m_tbl_terms.term_id 
        AND product_taxonomy_pa_j_m_tbl_termtaxonomy.taxonomy = 'pa_j-m'
        INNER JOIN wp_term_relationships AS rel_product_taxonomy_pa_j_m_tbl ON product_taxonomy_pa_j_m_tbl_termtaxonomy.term_taxonomy_id = rel_product_taxonomy_pa_j_m_tbl.term_taxonomy_id 
    ) AS product_taxonomy_pa_j_m_tbl ON product_taxonomy_pa_j_m_tbl.ID = wp_posts.id 
WHERE
    1 = 1 
    AND wp_posts.post_type = 'product'

How to improve the code so that filtering by product category works?

The operation of the code is shown on the website https://server515851.nazwa.pl/wordpress/wpn_hutniczy/o-magazynie/

The idea is to filter by categories in the tree on the left side of the page.

CodePudding user response：

This is another way to get WooCommerce products by category using the WP_Query class:

<?php
$cats_array = array(23); // Set here the ID of the selected category(ies)

$query_args = array(
    'posts_per_page' => 5, // How many products to show per page
    'post_type' => 'product',
    'post_status' => 'publish',
    'tax_query' => array(
        array(
            'taxonomy' => 'product_cat',
            'field' => 'id',
            'terms' => $cats_array
        )
    )
);

$products_by_category = new WP_Query($query_args);

// This category has published products, display them
if ( $products_by_category->have_posts() ) {
    ?>
    <ul class="product-list">
        <?php while ( $products_by_category->have_posts() ) : $products_by_category->the_post(); ?>
            <?php
            // Get product meta data.
            $stock = get_post_meta( get_the_ID(), '_stock', true );
            $price = get_post_meta( get_the_ID(), '_price', true );
            $weight = get_post_meta( get_the_ID(), '_weight', true );
            $sku = get_post_meta( get_the_ID(), '_sku', true );
            ?>
            <li>
                <a href="<?php the_permalink() ?>"><?php the_title(); ?></a>
                <p>Price: <?php echo $price; ?></p>
            </li>
        <?php endwhile; ?>
    </ul>

    <div class="product-list-pagination">
        <?php 
            echo paginate_links(
                array(
                    'base'         => str_replace( 999999999, '%#%', esc_url( get_pagenum_link( 999999999 ) ) ),
                    'total'        => $products_by_category->max_num_pages,
                    'current'      => max( 1, get_query_var( 'paged' ) ),
                    'format'       => '?paged=%#%',
                    'show_all'     => false,
                    'type'         => 'plain',
                    'end_size'     => 2,
                    'mid_size'     => 1,
                    'prev_next'    => true,
                    'prev_text'    => sprintf( '<i></i> %1$s', __( 'Previous', 'text-domain' ) ),
                    'next_text'    => sprintf( '%1$s <i></i>', __( 'Next', 'text-domain' ) ),
                    'add_args'     => false,
                    'add_fragment' => '',
                )
            );
        ?>
    </div>

    <?php
    // Reset the global $the_post
    wp_reset_query();
} // No products found
else {
    ?>
    <p>No products found under this category.</p>
    <?php
}

$cats_array is an array of product category ID(s). You need to populate this array somehow when the user selects one or more categories from the category tree on the left (via $_GET maybe? AJAX? You decide).

To see what the parameters in $query_args do please check out the WP_Query documentation for more details.

Finally, the if/else block renders a list of products by category (if found). You may need to change to adapt it to your needs of course.

As a bonus I added extra code to paginate the results (see paginate_links() for more).

If you have any particular questions about the code please feel free to ask.