Custom Filter

Thank you for your respond,

I actually visited it and play with it but I can’t figure out what to add in SQL, I only have little knowledge.

I added ACF Relationship Bidirectional to both Stores and Products.

I managed to pull products from a single Store detail page using the relationship I made same as the Product single detail page.

I already added the custom dropdown but I am stack on the SQL query which I can’t make the right statement.

Thank you for your reply.

I actually made it work.

For the custom sql it took me some time to realize how to catch the dropdown value

// Get Product Dropdown ID
	if (isset($_GET['product_and_store']) && $_GET['product_and_store'] != "0") {
		$product_id = $_GET['product_and_store'];
		$product_filter = "INNER JOIN $wpdb->postmeta AS prod_and_store ON prod_and_store.post_id = posts.ID AND prod_and_store.meta_key = 'product_and_store' ";
		$product_meta_value = "AND prod_and_store.meta_value LIKE '%$product_id%'";
	}

	// The default query that selects store location that fall within the selected radius. 
	$sql = "SELECT post_lat.meta_value AS lat,
	               post_lng.meta_value AS lng,
	               posts.ID,
	               ( %d * acos( cos( radians( %s ) ) * cos( radians( post_lat.meta_value ) ) * cos( radians( post_lng.meta_value ) - radians( %s ) ) + sin( radians( %s ) ) * sin( radians( post_lat.meta_value ) ) ) ) 
	            AS distance        
	          FROM $wpdb->posts AS posts 
	    INNER JOIN $wpdb->postmeta AS post_lat ON post_lat.post_id = posts.ID AND post_lat.meta_key = 'wpsl_lat'
	    INNER JOIN $wpdb->postmeta AS post_lng ON post_lng.post_id = posts.ID AND post_lng.meta_key = 'wpsl_lng' 
		$cat_filter
		$product_filter
	         WHERE posts.post_type = 'wpsl_stores' 
			 $product_meta_value
			 AND posts.post_status = 'publish' $sql_sort";

	return $sql;

took me also some time to get this dropdown to work

// Product Filter
$args = array(
    'post_type' => 'product',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'orderby' => 'title',
    'order' => 'ASC'
);
$options = array();
$products_array = get_posts($args);

$output .= "\t\t\t" . '<div id="custom-dropdown">' . "\r\n";
$output .= "\t\t\t\t\t" . '<label for="">' . esc_html($wpsl->i18n->get_translation('dropdown_label', __('Products', 'wpsl'))) . '</label>' . "\r\n";
$output .= "\t\t\t\t\t\t" . '<select data-name="product_and_store" name="product_and_store"  class="wpsl-dropdown wpsl-custom-dropdown" >';
$output .= "\t\t\t\t\t\t\t" . '<option value="0">Select Product</option>';
foreach ($products_array as $post) {
    $output .= "\t\t\t\t\t\t\t" . '<option value="' . $post->ID . '">' . $post->post_title . '</option>';
}
$output .= "\t\t\t\t\t\t" . '</select>';
$output .= "\t\t\t\t" . '</div>' . "\r\n";

PS: Products and Store Locator are in Bidirectional Relationship

• This reply was modified 4 years, 3 months ago by jcreatorz.