Support » Developing with WordPress » mysql query for category and tag intersection ( for a shortcode func )

  • I am trying to build a query for a wordpress function to show categories for products, only if those categories contain products with a specific tag; so the shortcode should be something like: [querytag tag_id=246]

    ( i will format the query to php when i have the mysql query right )

    What i have at the moment are two separate queries one to find all categories:

    SELECT wp_terms.* 
    FROM wp_terms 
    LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = 
    wp_term_taxonomy.term_id
         INNER JOIN wp_term_taxonomy AS ctax ON wp_terms.term_id = 
    ctax.term_id  
         WHERE wp_term_taxonomy.taxonomy = 'product_cat'

    and another that will find all products with a specific tag ( querytag ):

    SELECT posts.ID AS product_id,
       posts.post_title AS product_title
    FROM wp_posts AS posts,
         wp_terms AS terms,
         wp_term_relationships AS term_relationships,
         wp_term_taxonomy AS term_taxonomy
    WHERE term_relationships.object_id = posts.ID
      AND term_taxonomy.term_id = terms.term_id
      AND term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id
      AND posts.post_type = 'product'
      AND posts.post_status = 'publish'
      AND term_taxonomy.taxonomy = 'product_tag'
      AND terms.slug = 'querytag';

    but when i try to extract categories with products with a specific tag, i get an empty set:

    SELECT wp_terms.* 
    FROM wp_terms 
    LEFT JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
         INNER JOIN wp_term_taxonomy AS ctax ON wp_terms.term_id = ctax.term_id  
         WHERE wp_term_taxonomy.taxonomy = 'product_cat'
    AND wp_terms.slug = 'querytag';

    I currently have products with ‘querytag’ in the database. I want to try only display those products thereafter ( products with the tag specified ).

    can anybody steer me with this, I need to brush up on my sql.

    thank you

Viewing 9 replies - 1 through 9 (of 9 total)
  • Moderator bcworkz

    (@bcworkz)

    Such a query can be made using WP_Query class. No need to compose your own SQL. Or if want to anyway, use WP_Query first, then use its request property (the SQL it composed from your query args) as guidance for creating your own SQL.

    Thread Starter mirwordpress

    (@mirwordpress)

    thank you. My thinking was to try get the sql query working in php myadmin first and then compose it into the format required for the php function.

    i can’t seem to get the query to work.

    Thread Starter mirwordpress

    (@mirwordpress)

    SELECT wp_terms.* 
        FROM wp_terms 
       INNER JOIN wp_term_taxonomy ON wp_terms.term_id = wp_term_taxonomy.term_id
             INNER JOIN wp_term_taxonomy AS ctax ON wp_terms.term_id = ctax.term_id
             INNER JOIN wp_term_relationships AS crel ON crel.term_taxonomy_id = ctax.term_taxonomy_id
    INNER JOIN wp_posts AS posts ON posts.ID = crel.object_id
    INNER JOIN wp_term_relationships AS trel ON posts.ID = trel.object_id
    INNER JOIN wp_term_taxonomy AS ttax ON trel.term_taxonomy_id = ttax.term_taxonomy_id
    INNER JOIN wp_terms AS tags ON ttax.term_id = tags.term_id
             WHERE ctax.taxonomy = 'product_cat'
    AND ttax.taxonomy = 'product_tag'
    AND posts.post_type = 'product'
    AND posts.post_status = 'publish'
    AND tags.term_id = '%d'
    GROUP BY wp_terms.term_id = 247

    this is my current query – it’s not working as i expectded.

    • This reply was modified 2 months, 1 week ago by mirwordpress.
    Moderator bcworkz

    (@bcworkz)

    What results are you expecting? GROUP BY with an equality is not logical. Grouping is unlikely to give you the results you want. Maybe you want ORDER BY?

    Thread Starter mirwordpress

    (@mirwordpress)

    hi tried order by, its not working. I think it’s some simple tweak that I can’t work out. this is probably something very complex. if you know of a way of achieving it through the wp_query class pls let me know. I am currently looking into this.

    Moderator bcworkz

    (@bcworkz)

    Sorry, I’m still unclear exactly what you’re after. WP_Query can return posts with both a particular taxonomy term and specific meta data value. If you want all the taxonomy terms assigned to a particular single post, you can use wp_get_post_terms().

    Which terms are you hoping to group together? If the posts query is restricted to a specific term, they would all fall into one group. Grouping or ordering by a taxonomy term may not work as expected since posts can have multiple terms assigned and it’s difficult to manage which term to use in grouping because of the way the taxonomy tables are organized. This might be why WP_Query doesn’t support ordering by term. If each post only has one assigned term, then ordering by term is feasible, but it’d require customizing the SQL that WP comes up with via filter, or creating your own.

    Thread Starter mirwordpress

    (@mirwordpress)

    hi and thank you for your response. What i am trying to do is as follows: ( I think I may soon give up it seems impossible! )

    the website will cater for many different workplaces / industries but they will have the same categories in some cases, like ‘Hygiene’, ‘Workwear’. so for example, if a customer clicks on the workplace of Nursing Homes, the categories will show but when the category is clicked, like say they click ‘hygiene’; it will show the customer further categories and products for Hygiene that have products with the tag ‘nursing homes’. But the products will have many tags, like say ‘nursing homes’, ‘construction’, ‘kennels’ etc. So i want to show the customer relevant products for that workplace, and not show them something related like to construction hygiene, for example.

    I hope this explains it better but i think maybe i am trying to do something that has not been done before – or is impossible.

    pls let me know your thoughts.

    Moderator bcworkz

    (@bcworkz)

    Thanks for explaining further, it’s very helpful. The ways we can query by taxonomy term and meta tag is pretty powerful. I’m pretty sure getting related posts is feasible given the right information. Grouping and ordering is much less powerful, especially when posts have multiple terms assigned. It’s possible to do grouping beyond what WP_Query provides for, but then pagination will not work correctly.

    Aside from grouping, where the challenge lies is in extracting the information you need from the current context. Obviously we’ll know the category clicked on. We’ll know the chosen workplace only of it is the one currently queried by. If that is the case, get it from the current WP_Query object and include it in the category link URLs. The URL might look something like example.com/category/hygiene/?tag=nursing-home
    With a properly composed URL, you don’t even need a custom query, WP will know what to do just from the URL.

    Altering the default category URL is likely possible, but such ability is theme dependent. There’s a decent chance your theme uses get_category_link() to output a category URL. If so, the tag query string can be appended through the “category_link” filter. Otherwise what to do depends on what your theme does do for category links.

    If workplace isn’t what’s currently queried by, there’s no way to automatically know which to use if there could be more than one assigned per post.

    Thread Starter mirwordpress

    (@mirwordpress)

    hi and thank you for your reply.

    I have had to work on other projects and returned to this yesterday. I have tried another way and succeeded but not sure if my code is correct. I have used echo within the function as i couldn’t figure out any way to have the url to appear. I know echo should not be used within a shortcode function.

    would you have any advice for my code attempt?
    i also need to change this so that ‘testingtag’ can be added within the shortcode parameters.

    function display_products()
    {
     ob_start();
    
     $args = array( 
                'post_type'      => 'product', 
                'posts_per_page' => -1, 
                'product_tag'    => array('testingtag'),
                );
     $loop = new WP_Query( $args );
     $product_count = $loop->post_count;
    
     
    
     if( $product_count > 0 ){
        ?><div class="woocommerce columns-4"><ul class="products columns-4"> <?php
        while ( $loop->have_posts() ) : $loop->the_post(); 
                global $product;
                global $post;
    
                $loop->post->ID;
                 
                $product_cats = wp_get_post_terms( get_the_ID(), 'product_cat' );   
                     
                
                $post_slug = $post->post_name;
                
                foreach( $product_cats as $collection ) {   
                    $test = ''; 
                    $single_cat = '';         
                    $test .= $collection->slug;
                    if ( $product_cats && ! is_wp_error ( $product_cats ) ){
                        $single_cat = array_shift( $product_cats );  ?>
                        
                        <li class="product-category product brand_cats"><div class="banner-box"><div class="banner-box__image"> <a href="<? bloginfo( 'url' ); ?>/product-category/<? $test; ?>/">
                        <?php glana_woo_subcategory_thumbnail( $collection);  ?>           
                       
                    </a></div>
                        <div class="banner-box__info"><a href="<?php bloginfo( 'url' ); ?>/product-category/<?php $test; ?>/"><h2 itemprop="name" class="woocommerce-loop-category__title"><span><?php $single_cat->name; ?></a></span></h2></li><?php }  
                     }           
        endwhile;
        ?></ul></div> <?php
     }else{
           ?><p>No product matching your criteria.</p><?php
     }
    
     $result =  ob_get_clean();
     return $result;
     }
     add_shortcode('display_products_by_tag', 'display_products');
    • This reply was modified 1 month, 3 weeks ago by mirwordpress.
    • This reply was modified 1 month, 3 weeks ago by mirwordpress.
    • This reply was modified 1 month, 3 weeks ago by mirwordpress.
Viewing 9 replies - 1 through 9 (of 9 total)
  • You must be logged in to reply to this topic.