Get post featured image id with $wpdb

Question

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

Related Posts: