Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I'm not a sql expert, but with some help and copying from queries I've found on the web I got this working one that gets the 20 closest places (custom post type) to a specific latitude and longitude ($lat and $long). How can I edit this query to have also the post thumbnail ID in it? I want to do this to reduce the number of queries in my pages, so I think that looking for the value directly with this query instead of using get_post_thumbnail_id() each time I will save some, right?

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

Thanks a lot in advance to everyone!

share|improve this question
    
the post thumbnail id is stored in $wpdb->postmeta under column meta_value where meta_key = '_thumbnail_id' and post id is in column post_id. Maybe this will help you to write the query –  Mridul Aggarwal Oct 17 '12 at 9:37

1 Answer 1

up vote 1 down vote accepted

Haven't tested it, but this should work:

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, wpcfthumbnail AS thumbnail_id;
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)
    LEFT JOIN $wpdb->postmeta AS wpcfthumbnail ON ($wpdb->posts.ID = wpcfthumbnail.post_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'
AND wpcfthumbnail.meta_key = '_thumbnail_id'
ORDER BY DISTANCE
LIMIT 20

I added these bits of code:

, wpcfthumbnail AS thumbnail_id;
...
LEFT JOIN $wpdb->postmeta AS wpcfthumbnail ON ($wpdb->posts.ID = wpcfthumbnail.post_id)
...
AND wpcfthumbnail.meta_key = '_thumbnail_id'

Edit: This works.

SELECT $wpdb->posts.ID, $wpdb->posts.post_title, $wpdb->terms.name, getthumbinfo.meta_value AS metainfo,
    thelat.meta_value AS latitude,thelong.meta_value AS longitude, 
    ASIN ( SQRT (POWER(SIN(($lat - thelat.meta_value)*pi()/180 / 2),2) + COS($lat * pi()/180) * COS(thelat.meta_value *pi()/180) * POWER(SIN(($long - thelong.meta_value) *pi()/180 / 2), 2) ) ) AS distance
FROM $wpdb->posts
    LEFT JOIN $wpdb->postmeta AS thelong ON ($wpdb->posts.ID = thelong.post_id)
    LEFT JOIN $wpdb->postmeta AS thelat ON ($wpdb->posts.ID = thelat.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)
    LEFT JOIN $wpdb->postmeta AS getthumbid ON ($wpdb->posts.ID = getthumbid.post_id)
    LEFT JOIN $wpdb->postmeta AS getthumbinfo ON (getthumbid.meta_value = getthumbinfo.post_id)
WHERE $wpdb->posts.ID NOT IN ($post->ID)
    AND thelat.meta_key = 'wpcf-latitude'
    AND thelong.meta_key = 'wpcf-longitude'
    AND $wpdb->posts.post_status = 'publish' 
    AND $wpdb->posts.post_type = 'places'
    AND $wpdb->term_taxonomy.taxonomy = 'countries'
    AND getthumbid.meta_key = '_thumbnail_id'
    AND getthumbinfo.meta_key = '_wp_attachment_metadata'
ORDER BY distance
LIMIT 12
share|improve this answer
    
thank you, but nope, it's not working... I also did something similar following Mridul Aggarwal comment, but without success. How can I see if there is a sql error in the query? –  Stefano Oct 17 '12 at 10:45
    
IT WORKS! There was just a small fix to do in the query... the first line of code you added should be "wpcfthumbnail.meta_value AS thumbnail_id" instead of "wpcfthumbnail AS thumbnail_id" ;) Here you can find the final working version of the query! Thanks! –  Stefano Oct 19 '12 at 8:28
    
@Stefano, right, glad it worked out :). –  Dogbert Oct 19 '12 at 11:43
    
forgot a pastebin link to the final query version: pastebin.com/muaDNSRh –  Stefano Oct 19 '12 at 12:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.