Support » Plugins » Hacks » Get post featured image id with $wpdb in a complex query

  • Resolved Stefano


    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->,
        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'
    LIMIT 20

    Thanks a lot in advance to everyone!

Viewing 5 replies - 1 through 5 (of 5 total)
  • Moderator bcworkz


    Yes, it would be more efficient. The image ID is stored in the postmeta table with the key ‘_thumbnail_id‘. I’m no sql expert either, so I shouldn’t be guessing at the proper code, but since you’ve joined the postmeta table and are selecting values as it is, you could probably guess as well as I and stand a good chance of getting a proper result.

    For the sake of efficiency, you don’t really need the “6371 * 2 *” portion of the distance calculation. You only need a relative number, not a real world unit of measure. You get the same items returned either way. Not that it makes much difference. Just sayin’.

    Clever query in any case. Nice work!

    I’ve tryed adding those lines but it’s not working:

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

    and I can’t figure out how to do it… 🙁 the actual query is the result of some cut&paste, and some changes made following a few suggestions I’ve found

    Also, I’m doing the 6371*2 calculation because I’m showing the distance on the website. Do you think that it is better to calculate the real unit of measure with php than in the query?

    Moderator bcworkz


    So much for my idea of blindly mimicking the working format. I think your first line needs to be
    SELECT .... wpcfthumbnail.meta_value AS thumbnail_id;
    If that doesn’t help, hopefully someone who actually knows sql will be able to help. Sort of the blind leading the blind here so far.

    I think it would be better to apply the constants in php to the 20 results rather than have sql apply it to every record it matched in order to find the 20 closest. But if that confuses things, I’m not sure it’s worth the tiny time savings.

    Damn, you’re right!!!! I often fail on noticing details….

    For the sake of completion, this is the final working query:

    I think I can do better by finding out also the src url of the featured image (thumbnail size), so I don’t have to use wp_get_attachment_image_src each time in the loop, saving a few more queries.

    Maybe I’ll work on it over the weekend and post the results here! 😉

    Ok, I wasn’t able to wait 🙂

    With this I’ve saved a lot of other queries (before the query count was near 100, now it is 63 (I also use the WordPress Transients API to store this value with an expiration time of 24h)

    I know, it’s still a lot… maybe I should start using $wpdb in a couple of other places outside the loop!)

    Then to get the thumbnail url and size I’m doing this:

    $thumb = unserialize($place->metainfo);
    $thumb_src = $thumb['sizes']['thumbnail']['file'];
    $thumb_height = $thumb['sizes']['thumbnail']['height'];
    $thumb_width = $thumb['sizes']['thumbnail']['width'];

    I hope that someone will find this useful! 😉

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Get post featured image id with $wpdb in a complex query’ is closed to new replies.