mysql headache nightmare! (multiple meta_keys)

  • RSimpson

    (@rsimpson)


    13 years, 1 month 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

Viewing 15 replies - 1 through 15 (of 16 total)
1 2
  • Thread Starter RSimpson

    (@rsimpson)

    13 years ago

    Nobody?

    Thread Starter RSimpson

    (@rsimpson)

    13 years ago

    *bump*

    Thread Starter RSimpson

    (@rsimpson)

    13 years ago

    *bump*

    Thread Starter RSimpson

    (@rsimpson)

    13 years ago

    *bump*

    Nobody at all?

    Spencer Hill

    (@ws5f3dj7)

    13 years 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.

    Thread Starter RSimpson

    (@rsimpson)

    13 years 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

    Thread Starter RSimpson

    (@rsimpson)

    13 years ago

    *bump*

    Thread Starter RSimpson

    (@rsimpson)

    12 years, 12 months ago

    *bump*

    Thread Starter RSimpson

    (@rsimpson)

    12 years, 8 months ago

    *bump*

    popper

    (@julialasarte)

    12 years, 8 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)
    Thread Starter RSimpson

    (@rsimpson)

    12 years, 8 months ago

    Hi popper,

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

    Cheers,
    Rab

    popper

    (@julialasarte)

    12 years, 8 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.

    Thread Starter RSimpson

    (@rsimpson)

    12 years, 8 months ago

    Ah excellent!

    I’ll give it a bash and report back, thanks again 🙂

    Thread Starter RSimpson

    (@rsimpson)

    12 years, 8 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
";
    popper

    (@julialasarte)

    12 years, 8 months ago

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

Viewing 15 replies - 1 through 15 (of 16 total)
1 2
  • The topic ‘mysql headache nightmare! (multiple meta_keys)’ is closed to new replies.