I'm trying to order my posts by the nearest location to a user.
To solve this issue I created a table called geolocation that contains all latitude(lat) and longditude(long) of by post_id.
In order to sort the posts I used the following hook: woocommerce_get_catalog_ordering_args
The issue I'm facing Is that I need to join the table I created so I can order the products by lat and long (ORDER BY (POW((geodata.long-9.9533548),2) + POW((geodata.lat-49.7913044),2)) ASC)
Using various examples this is what I've came up with - however I'm receiving a 500 Internal error and I'm unsure how to get the join working so I can order by attributes in another table:
add_filter( 'woocommerce_catalog_orderby', 'add_custom_sorting', 99999);
function add_custom_sorting( $options) {
$options['distance'] = 'Nach Standort sortieren';
return $options;
}
add_filter( 'woocommerce_get_catalog_ordering_args', 'custom_sorting_for_distance' );
function custom_sorting_for_distance( $args ) {
if ( isset( $_GET['orderby'] ) && 'distance' === $_GET['orderby'] ) {
add_filter('posts_clauses', 'posts_clauses_add_join_and_filter', 10, 2);
$lat = sanitize_text_field('49.7913044');
$lng = sanitize_text_field('9.9533548');
$args['order'] = 'ASC';
$args['meta_key'] = '';
$args['orderby'] = " (POW((geodata.long-{$lng}),2) + POW((geodata.lat-{$lat}),2)) ";
}
return $args;
}
function posts_clauses_add_join_and_filter( $clauses, $wp_query ) {
global $wpdb;
$lat = sanitize_text_field('49.7913044');
$lng = sanitize_text_field('9.9533548');
$clauses['join'] = " LEFT JOIN 4rqJ9_geodata AS geodata ON 4rqJ9_posts.ID = geodata.post_id ";
$clauses['orderby'] = " (POW((geodata.long-{$lng}),2) + POW((geodata.lat-{$lat}),2)) ";
return $clauses;
}
Has anyone got any idea how to fix this issue? I'm pretty sure that I just need the correct way to join the tables.
This is a SQL Statement that worked fine in the PHPmyADMIN backend and that I am trying to replecate in WooCommerce.
SELECT 4rqJ9_posts.*
FROM 4rqJ9_posts
INNER JOIN 4rqJ9_postmeta
ON ( 4rqJ9_posts.ID = 4rqJ9_postmeta.post_id )
INNER JOIN 4rqJ9_postmeta AS mt1
ON ( 4rqJ9_posts.ID = mt1.post_id )
INNER JOIN 4rqJ9_postmeta AS mt2
ON ( 4rqJ9_posts.ID = mt2.post_id )
LEFT JOIN 4rqJ9_geodata AS geodata
ON 4rqJ9_posts.ID = geodata.post_id
WHERE 1=1
AND 4rqJ9_posts.post_type = 'product'
AND ((4rqJ9_posts.post_status = 'publish'))
GROUP BY 4rqJ9_posts.ID
ORDER BY (POW((geodata.long-9.9533548),2) + POW((geodata.lat-49.7913044),2)) ASC
source https://stackoverflow.com/questions/70657220/woocommerce-adding-a-left-join-to-after-woocommerce-get-catalog-ordering-args
Comments
Post a Comment