Woocommerce Vendors Set Selling Locations and Manipulate Product Query Loop

Question

I want to only list products that can be shipped to a customer based on their location set in billing/shipping address. (All my users are logged in)

Each of my vendors may ship to certain countries only so I don't want to show products to a user that cannot be shipped to them.

To tackle this problem I have added an extra field in the edit vendor page which saves the countries (via multi select box) they can ship to as a separate term meta.

update_term_meta($term_id, 'vendor_data_shipping_countries', $selected_shipping_countries);

etc...

All that data saves fine and is outputted as follows when I call get_term_meta($term->term_id, 'vendor_data_shipping_countries')[0].

Array
(
    [0] => FR
    [1] => GB
)

What I am having trouble with now is filtering the product loop query to only show products that can be shipped to the user with the action 'woocommerce_product_query'.

function ac_vendor_show_deliverable_products($query)
{
    // magical query filter here...
    // if users location matches any of the vendor products ship to countries then output the product to the user
    // $query->set(); ... something...
}
add_action('woocommerce_product_query','ac_vendor_show_deliverable_products');

This is where my skill level fails me. I am fairly new to WC and not good at manipulating the query with actions. Better at just writing the full SQL but feel I would mess lots of other things up and filtering is the best way to go.

I expect someone's kunfu is way stronger than mine! Can anyone figure this out?

Hope someone can help.

UPDATE:

I have managed to write exactly what I want to happen in SQL

SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  

LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_taxonomy ON (wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id) 
LEFT JOIN wp_termmeta ON (wp_termmeta.term_id = wp_term_taxonomy.term_id)

WHERE wp_termmeta.meta_key = 'vendor_data_shipping_countries'
AND wp_termmeta.meta_value LIKE '%"GB"%'

AND wp_posts.post_type = 'product' 
AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC LIMIT 0, 12

This only lists products that have added GB as a country they can ship to.

Note the meta_value is stored as a serialized array so the easiest way to match was to perform a LIKE as values are stored as a:2:{i:0;s:2:"FR";i:1;s:2:"GB";} for example.

If anyone can figure out how to put that SQL into the woocommerce_product_query hook then that would be amazing. But I can't for the life of me figure out how this is possible...

Everything on https://codex.wordpress.org/Class_Reference/WP_Query just adds SQL for wp_postmeta not wp_termmeta from what I can see.

Cheers

Answer 1

I managed to get this to work by using the posts_join and posts_where filters instead.

I hope this helps someone else down the line.

/*
 * Add the needed term tables
 */
function ac_vendor_sql_join_term_meta($join)
{
    global $wp_query, $wpdb;

    //only do this is WC product query
    if(isset($wp_query->query_vars['wc_query']) && $wp_query->query_vars['wc_query'] == 'product_query')
    {
        $join .= ' LEFT JOIN '. $wpdb->term_relationships .' tr1 ON (tr1.object_id = '. $wpdb->posts .'.ID)';
        $join .= ' LEFT JOIN '. $wpdb->term_taxonomy .' tt1 ON (tt1.term_taxonomy_id = tr1.term_taxonomy_id)';
        $join .= ' LEFT JOIN '. $wpdb->termmeta .' tm1 ON (tm1.term_id = tt1.term_id)';
    }

    return $join;
}
add_filter('posts_join', 'ac_vendor_sql_join_term_meta');

/*
 * Add the needed where statements
 */
function ac_vendor_sql_filter_shipping_where($where, $wp_query)
{
    //only do this is WC product query
    if(isset($wp_query->query_vars['wc_query']) && $wp_query->query_vars['wc_query'] == 'product_query')
    {
        //get the users billing country code.
        if(is_user_logged_in())
        {    
            $billing_country = get_user_meta(get_current_user_id(), 'billing_country', TRUE);
        }
        else //default to IP location
        {
            $geo_locate         = WC_Geolocation::geolocate_ip($_SERVER['REMOTE_ADDR']);
            $billing_country    = $geo_locate['country'];
        }

        $where .= " AND tm1.meta_key = 'vendor_data_shipping_countries'";
        $where .= " AND tm1.meta_value LIKE '%\"". $billing_country ."\"%'"; 
    }

    return $where;
}
add_filter('posts_where', 'ac_vendor_sql_filter_shipping_where', 10, 2);