WordPress.org

1. Rab
   Member
   Posted 1 year ago #

   Hi folks,

   A bit of background info:
   I'm building a theme for a band and this theme is going to include tour posters as featured images within the custom post type of 'concerts'.
   I already have the concert listings working as I'd like, using a custom field '_event_date' to determine the order in which they're displayed and whether they're displayed at all (no concerts prior to today's date are displayed).

   This was all well and good until I tried to have it check for the existence of '_thumbnail_id' as a meta_key also and now within the query the meta_key for _thumbnail_id is cancelling out the meta_key for _event_date.

   Not good.

   Here's the code I'm using just now to get the upcoming concerts out of the database:

   global $wpdb;
   $blog_prefix = $wpdb->prefix;
   $today = strtotime("today");
   
   $upcoming_concert_posters_query = "
   	SELECT *
   	FROM ".$blog_prefix."posts, ".$blog_prefix."postmeta
   	WHERE ID = post_id
   	AND post_type = 'concerts'
   	AND meta_key = '_event_date'
   	AND meta_value >= ".$today."
   	AND post_status = 'publish'
   	ORDER BY meta_value ASC
   	LIMIT 100
   ";
   
   $upcoming_concert_posters = $wpdb->get_results($upcoming_concert_posters_query, OBJECT);

   How can I change this to retain this current functionality but add an additional conditional which checks for the existence of _thumbnail_id?

   As you might have guessed, I suck with MySQL queries and any help would be greatly appreciated :)

   Cheers,
   Rab

2. Rab
   Member
   Posted 1 year ago #

   Nobody?

3. Rab
   Member
   Posted 1 year ago #

   *bump*

4. Rab
   Member
   Posted 1 year ago #

   *bump*

5. Rab
   Member
   Posted 1 year ago #

   *bump*

   Nobody at all?

6. Spencer Hill
   Member
   Posted 1 year ago #

   It appears that you're ordering by today's date, not the date of the concert. Correct me if I'm wrong though.

   My team and I developed this website for a small local band, if you'd like I'd be happy to give you access to the code as we spent a LOT of time preparing the plugins and themes.

7. Rab
   Member
   Posted 1 year ago #

   Hi Spencer,

   Thanks for your reply. My order by uses the meta_value. The AND meta_value >= ".$today." part you're noticing just makes sure that the value of _event_date is greater than or equal to $today which is defined at the top.

   The big problem here I think is that I'm trying to narrow the query down further using an additional meta_key (_thumbnail_id), I don't know how to narrow what I've got (all posts where _event_date's value is greater than or equal to today's timestamp) down to just those where _event_date's value is greater than or equal to today's timestamp AND has a _thumbnail_id value.

   Any ideas how I could trim down the results?

   Cheers,
   Rab

8. Rab
   Member
   Posted 1 year ago #

   *bump*

9. Rab
   Member
   Posted 1 year ago #

   *bump*

10. Rab
    Member
    Posted 9 months ago #

    *bump*

11. popper
    Member
    Posted 9 months ago #

    The problem is each of those values you are checking for is a different entry on the wp_postmeta table, so you'll never find a row with both '_thumbnail_id' and '_event_date' set at the same time. You'll need to check for the existence of the _thumbnail_id with EXISTS(). Like:

    AND EXISTS( EXISTS(SELECT *
                        FROM   wp_postmeta
                        WHERE  wp_postmeta.meta_key = '_thumbnail_id'
                               AND wp_postmeta.post_id = wp_posts.ID)

12. Rab
    Member
    Posted 9 months ago #

    Hi popper,

    Thanks for your reply. Where abouts should I add that to the query? Just before LIMIT?

    Cheers,
    Rab

13. popper
    Member
    Posted 9 months ago #

    It should go before the order by, since it'll be part of WHERE:

    $upcoming_concert_posters_query = "
    	SELECT *
    	FROM ".$blog_prefix."posts, ".$blog_prefix."postmeta
    	WHERE ID = post_id
    	AND post_type = 'concerts'
    	AND meta_key = '_event_date'
    	AND meta_value >= ".$today."
    	AND post_status = 'publish'
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_thumbnail_id'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
    	ORDER BY meta_value ASC
    	LIMIT 100
    ";

    That's untested, but it should work.

14. Rab
    Member
    Posted 9 months ago #

    Ah excellent!

    I'll give it a bash and report back, thanks again :)

15. Rab
    Member
    Posted 9 months ago #

    While I'm here actually, if I wanted to use this multiple times (with a different meta_key for each one) within the same query could I just add them as extra AND EXISTS lines?

    Like this?:

    $upcoming_concert_posters_query = "
    	SELECT *
    	FROM ".$blog_prefix."posts, ".$blog_prefix."postmeta
    	WHERE ID = post_id
    	AND post_type = 'concerts'
    	AND meta_key = '_event_date'
    	AND meta_value >= ".$today."
    	AND post_status = 'publish'
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_thumbnail_id'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_something_else'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
            AND EXISTS( SELECT *
                        FROM   ".$blog_prefix."postmeta
                        WHERE  ".$blog_prefix.".meta_key = '_another_thing'
                               AND ".$blog_prefix.".post_id = ".$blog_prefix."posts.ID)
    	ORDER BY meta_value ASC
    	LIMIT 100
    ";

16. popper
    Member
    Posted 9 months ago #

    Sure. There's probably a better way somewhere, but that should work.

17. Rab
    Member
    Posted 8 months ago #

    Hi popper,

    Just a quick update to let you know that your code worked, I only had to make a couple of tiny minor adjustments (the term 'postmeta' needed to be in front of .meta_key and .post_id)

    :)

    I'm going to try adding multiple keys and let you know how I get on.

    Thanks again!

    Cheers,
    Robert

Topic Closed

This topic has been closed to new replies.