Get multiple custom field values in a $wpdb query

2 Views

I want to create a $wpdb query that select the closest places (custom post type) near the current coordinates ($lat and $long) but I’m not able to get two custom field values simoultaneously (wpfc-latitude and wpfc-longitude)…

how can i change my query to get also the longitude?

Read More
SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->terms.name, $wpdb->postmeta.meta_value AS latitude
FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->postmeta.meta_key = 'wpcf-latitude'
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'places'
AND $wpdb->term_taxonomy.taxonomy = 'countries'

UPDATE – THE FIXED QUERY

Thanks to s_ha_dum I succesfully have both values and I can now calculate the distance and order my result. My final query is: 

SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->terms.name, wpcflat.meta_value AS latitude, wpcflong.meta_value AS longitude,
    6371 * 2 * ASIN ( SQRT (POWER(SIN(($lat - wpcflat.meta_value)*pi()/180 / 2),2) + COS($lat * pi()/180) * COS(wpcflat.meta_value *pi()/180) * POWER(SIN(($long - wpcflong.meta_value) *pi()/180 / 2), 2) ) ) as distance
FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta as wpcflong ON ($wpdb->posts.ID = wpcflong.post_id)
    LEFT JOIN $wpdb->postmeta as wpcflat ON ($wpdb->posts.ID = wpcflat.post_id)
    LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
    LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
    LEFT JOIN $wpdb->terms ON($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE $wpdb->posts.ID NOT IN ($post->ID)
AND wpcflat.meta_key = 'wpcf-latitude'
AND wpcflong.meta_key = 'wpcf-longitude'
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'places'
AND $wpdb->term_taxonomy.taxonomy = 'countries'
ORDER BY distance
LIMIT 20

Is it a heavy query to load? Do you know if is there a way to optimize it?

Post Views: 2

Related posts

Leave a Reply

1 comment

  1. You are going to have to JOIN twice on the postmeta table. Something like:

    SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->terms.name, wpcflat.meta_value AS latitude, wpcflong.meta_value AS longitude
FROM $wpdb->posts
/* First Join */
LEFT JOIN $wpdb->postmeta as wpcflong ON $wpdb->posts.ID = wpcflong.post_id
/* Second Join */
LEFT JOIN $wpdb->postmeta as wpcflat ON $wpdb->posts.ID = wpcflat.post_id
LEFT JOIN $wpdb->term_relationships ON($wpdb->posts.ID = $wpdb->term_relationships.object_id)
LEFT JOIN $wpdb->term_taxonomy ON($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)
LEFT JOIN $wpdb->terms ON ($wpdb->term_taxonomy.term_id = $wpdb->terms.term_id)
WHERE 1
/* Meta queries for lat and long */
AND wpcflat.meta_key = 'wpcf-latitude'
AND wpcflong.meta_key = 'wpcf-longitude'
/* I don't know what criteria you are using to calculate 'nearest' 
but you'd need more conditions here for that */
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'places'
AND $wpdb->term_taxonomy.taxonomy = 'countries'

    As noted in my SQL comments, I don’t know what you are using as criteria for ‘closest’ but that code will get you both meta values to play with. My database doesn’t have lat and long data so I can’t test that strictly speaking but it is correct in principle, barring a typo or two or other stupid mistake.

    Subqueries are another possible solution but they do not perform well when you try to sort or filter on them.