sql query from multiple meta keys and sort?

  • Resolved haagendazs1

    (@haagendazs1)


    14 years, 8 months ago

    Hi, I’ve been trying to figure this out for a while:

    So I have a bunch of posts with variable amount of custom fields like so:

    Post | key1 | key2 | key3
    1 | X | we | er
    2 | X | | df
    3 | Y | xc |

    some posts don’t have values for every key.

    I want to a SELECT statement that finds all posts with key1:X, and then sort these posts alphabetically based on their meta values of key2/key3.

    I have something like this so far:

    SELECT DISTINCT wp_posts.*
    FROM wp_postmeta, wp_posts
    WHERE wp_posts.ID = wp_postmeta.post_id
    AND wp_postmeta.meta_value = ‘X’
    ORDER BY wp_postmeta.meta_value

    This does select all correct posts (the ones where key1:X) but sorts them in some kind of other order (it’s not random, and it’s consistent, but I don’t know what this order is – not alphabetically though).

    What am I missing?? Any help would be greatly appreciated!

Viewing 4 replies - 1 through 4 (of 4 total)
  • Thread Starter haagendazs1

    (@haagendazs1)

    Sorry I mistyped what I meant above.

    I’m trying to pull all posts with any meta_value = ‘df’ and sort these by the meta_value where the meta_key = ‘X’

    =)

    Thread Starter haagendazs1

    (@haagendazs1)

    In general terms, I’m trying to select posts with a specific meta_value, then sort using a specific meta_key’s meta_value….

    Thread Starter haagendazs1

    (@haagendazs1)

    Nevermind I figured it out. For anyone who might also be in the same situation, I used a subquery that identifies the wp_postmeta.post_id first:

    SELECT wp_posts.*, wp_postmeta.*
    FROM wp_postmeta, wp_posts
    WHERE wp_posts.ID = wp_postmeta.post_id
    AND wp_posts.post_status = ‘publish’
    AND wp_posts.post_type = ‘post’
    AND wp_postmeta.post_id IN (
    SELECT wp_postmeta.post_id
    FROM wp_postmeta, wp_posts
    WHERE wp_posts.ID = wp_postmeta.post_id
    AND wp_posts.post_status = ‘publish’
    AND wp_posts.post_type = ‘post’
    AND wp_postmeta.meta_value = ‘something’)
    AND wp_postmeta.meta_key = ‘X’
    ORDER BY wp_postmeta.meta_value ASC

    Haagendazs1 I’ve been looking for a way to do queries in WP, and it seems like you have a great solution. Do you know of any other plug-ins that do this so I don’t have to write any code? I’m not super familiar with PHP yet to do it. I’m looking to create a site kind of like Blurtit.com or Answers.com. Any suggestions?

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘sql query from multiple meta keys and sort?’ is closed to new replies.