SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Join with one to many

    Hello,

    Trying to create a query that's a bit over my head. Working in WordPress that stores meta data for each post in a separate table like...

    Table 1 - wp_posts
    - Sample Columns: post_id, title, content

    Table 2 - wp_postmeta
    - Columns: post_id, meta_key, meta_value.

    When you have more than one meta data entry, a straight query on the wp_postmeta by post_id would give you...

    Row 1: post_id=x, meta_key=custom_field_1, meta_value=custom_value_1
    Row 2: post_id=x, meta_key=custom_field_2, meta_value=custom_value_2

    What I'm trying to get, via one query, is a row returned for each post like...

    Row 1: post_id=x, custom_field_1=custom_value_1, custom_field_2=custom_value_2.

    Can someone help point me in the right direction? I'd sure appreciate it!

    Philip

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what you're asking for can be done in sql, but it's clunky, and it depends on the number of meta fields, as you have to hardcode something for each one, so it's different if there are two columns versus three columns, etc.

    why can't you just consume multiple meta rows?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your help.

    Maybe I can do multiple rows, but I'm not sure how. This is for an instant search feature on a table with 10k's of rows, so it gets executed many times per visitor and obviously needs to be highly optimized, ie I can't be gathering all the wp_postmeta info for each record in a loop.

    Do you mean just let it return multiple rows with the same wp_post info and the meta_key and meta_value would be different for each row? I guess I could just loop through all the results and pack a single array for each post with all the wp_postmeta info. Is something like that what you mean?

    Also, I don't mind clunky as long as it's efficient, and the number of fields from wp_postmeta will always be the same. I've come close with...

    SELECT wp_posts.ID, wp_posts.post_date, wp_posts.post_title, wp_posts.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts
    LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
    WHERE wp_posts.post_status = 'publish'
    GROUP BY wp_posts.ID
    ORDER BY wp_posts.post_date DESC
    LIMIT 30

    ...which works fine, but throws an unknown column error when I try adding "AND ad_miles_range = '50'". Might I be on the right path with this approach? Would be much easier than trying to create arrays and all that.

    Thanks again, I really appreciate it!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wpcs_phil View Post
    Do you mean just let it return multiple rows with the same wp_post info and the meta_key and meta_value would be different for each row? I guess I could just loop through all the results and pack a single array for each post with all the wp_postmeta info. Is something like that what you mean?
    this is it, exactly

    what you were doing here --

    ... MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,

    this is what i meant when i said "hardcoding"

    you have to do it for each different field, and your query will have to be touched if you ever add another different type of field

    ("touching" a query might not be a big deal for a one-man shop, but in a large organization, the production department takes a very, very dim view of changes to queries, requiring you to dance through all kinds of hoops before the change goes live...)

    ...which works fine, but throws an unknown column error when I try adding "AND ad_miles_range = '50'"
    you'd have to use a HAVING condition, not a WHERE condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You so rock. The hardcoded version is much preferred, it's just me building this so having to change a query isn't nearly as big of a deal as messing with packing arrays and the extra processing that will take. Getting there on the clunky though.

    Just a couple little problems and I think it will be there. Code....

    SELECT wp_posts.ID, wp_posts.post_date, wp_posts.post_title, wp_posts.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts
    LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
    GROUP BY wp_posts.ID
    HAVING MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
    ORDER BY wp_posts.post_date DESC
    LIMIT 30

    How do I incorporate another HAVING clause, ie 'ad_state' = 'nj'?

    Also, I do need some 'where' type stuff, ie 'where wp_posts.post_status="publish"'. How do I get this in there too?

    You are a life saver! Thanks so much.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    incorporate additional HAVING conditions the same way you'd incorporate additional WHERE conditions -- with ANDs and ORs

    WHERE conditions for filter purposes should be placed right before the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Soo close, this works...

    SELECT wp_posts.ID, wp_posts.post_date, wp_posts.post_title, wp_posts.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts
    LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
    WHERE wp_posts.post_status = 'publish'
    GROUP BY wp_posts.ID
    HAVING (MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
    ORDER BY wp_posts.post_date DESC
    LIMIT 30

    ...and returns everything right, including rows with ad_state = "NJ". When I add and AND under the HAVING, like...

    SELECT wp_posts.ID, wp_posts.post_date, wp_posts.post_title, wp_posts.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts
    LEFT JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
    WHERE wp_posts.post_status = 'publish'
    GROUP BY wp_posts.ID
    HAVING MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
    AND MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END) = 'NJ'
    ORDER BY wp_posts.post_date DESC
    LIMIT 30

    ...it returns no results. Do I need to do it a little different?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    maybe you're getting no results because there aren't any for those criteria?

    if you think that there are, perhaps you could dump the table structure along with a few rows so that i can do some testing...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are rows that match, ie 50 miles and NJ. I see NJ in there when I just do 50 miles.

    Query for a sample data dump attached that should be enough to sort it out.
    Attached Files Attached Files

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i tested with your data, and i got back 40967 as expected
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, should have tested that myself. Not the same result in the real data though, so I must have oversimplified the sample.

    Is there anyway to get you the full dumps? They are rather large (90k and 10k rows) so not sure they will attach here.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    not sure that the full dumps would help any more than looking at specific examples

    why don't you do some digging, find a couple of posts which aren't working the way you expect, and pull those INSERT statements out of your dump file
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heya,

    Thanks so much for your help. Please send me an Amazon link for a book, cd, dvd, or such

    Alrighty, so I figured out how to dump from a query (for those who don’t know, just run a query in phpmyadmin and then click export at the bottom of the results page) and can recreate the problem now on a sample. Attached (tables are called wp_posts_3 and wp_postmeta_3 now).

    So, if I run…

    SELECT wp_posts_3.ID, wp_posts_3.post_date, wp_posts_3.post_title, wp_posts_3.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts_3
    LEFT JOIN wp_postmeta_3 ON ( wp_posts_3.ID = wp_postmeta_3.post_id )
    WHERE wp_posts_3.post_status = 'publish'
    GROUP BY wp_posts_3.ID
    HAVING MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
    ORDER BY wp_posts_3.post_date DESC
    LIMIT 30

    …then I get 2 rows, one that has ‘TN’ as the ad_state. If I run now…

    SELECT wp_posts_3.ID, wp_posts_3.post_date, wp_posts_3.post_title, wp_posts_3.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts_3
    LEFT JOIN wp_postmeta_3 ON ( wp_posts_3.ID = wp_postmeta_3.post_id )
    WHERE wp_posts_3.post_status = 'publish'
    GROUP BY wp_posts_3.ID
    HAVING MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
    AND MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END) = 'TN'
    ORDER BY wp_posts_3.post_date DESC
    LIMIT 30

    …just adding the ad_state HAVING AND, then I get an empty result. If I just run…

    SELECT wp_posts_3.ID, wp_posts_3.post_date, wp_posts_3.post_title, wp_posts_3.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts_3
    LEFT JOIN wp_postmeta_3 ON ( wp_posts_3.ID = wp_postmeta_3.post_id )
    WHERE wp_posts_3.post_status = 'publish'
    GROUP BY wp_posts_3.ID
    HAVING MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END) = 'TN'
    ORDER BY wp_posts_3.post_date DESC
    LIMIT 30

    …I still get an empty result. If I run it with 2 parameters other than ad_state it works right again, ie…

    SELECT wp_posts_3.ID, wp_posts_3.post_date, wp_posts_3.post_title, wp_posts_3.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts_3
    LEFT JOIN wp_postmeta_3 ON ( wp_posts_3.ID = wp_postmeta_3.post_id )
    WHERE wp_posts_3.post_status = 'publish'
    GROUP BY wp_posts_3.ID
    HAVING MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
    AND MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END) = 'Brighton'
    ORDER BY wp_posts_3.post_date DESC
    LIMIT 30

    Seems to be something specific to the ad_state field. Any ideas?

    Hopefully you can find the issue from this, I’ve spent hours and hours since yesterday and still no luck.

    Thanks yet again!

    Philip
    Attached Files Attached Files

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    look at postmeta with meta_id = 95786

    that state is not TN, it is blankTN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahhhhhhhhhhhhhhhhhhh.

    Can't BELIEVE I didn't find that. Got to clean up my importer (moving from an old system to a new currently) and try 'er again.

    Seriously, send an Amazon link ok?

    Off to get that resolved!

  16. #16
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alrighty, that was indeed the problem. Purrrrfect. Guess I was staring at it too long...lol.

    One more issue to go with this. I also need to select the first image that goes with each post. I'm trying to leave WP as untouched as possible, but I'm not even sure it's possible with how it stores the images, which is in the same table as the posts, but a different row. It also stores autosaves and revisions to a post as a new row in the same table.

    Is this even possible to do all in one query? The query is already beyond me, so incorporating this has been very unfruitful. Any ideas or pointing me in the right direction would be hugely appreciated.

    An new dump is attached with 1 post that has an attachment, autosave, and revision) along with it's associated wp_postmeta rows.

    Current queries is like...

    SELECT wp_posts_3.ID, wp_posts_3.post_date, wp_posts_3.post_title, wp_posts_3.post_content,
    MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city,
    MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state,
    MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for,
    MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category,
    MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range,
    MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code,
    MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
    FROM wp_posts_3
    LEFT JOIN wp_postmeta_3 ON ( wp_posts_3.ID = wp_postmeta_3.post_id )
    WHERE wp_posts_3.post_status = 'publish'
    GROUP BY wp_posts_3.ID
    HAVING MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
    AND MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END) = 'NJ'
    ORDER BY wp_posts_3.post_date DESC
    LIMIT 30

    Fingers crossed, thanks yet again for all your help...

    Philip
    Attached Files Attached Files

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by wpcs_phil View Post
    I'm not even sure it's possible with how it stores the images, which is in the same table as the posts, but a different row. It also stores autosaves and revisions to a post as a new row in the same table.
    could you explain how the rows are related?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah man, just realized you replied days ago. Been banging my head with it still.

    WP stores posts, pages, revisions, auto-saves, and attachments all in the wp_posts table. Sample data of two lines, one a post and one an

    attachment to that post...

    Row 1: Post
    ID: 21877
    post_status: publish
    post_parent: 0
    post_type: page
    post_mime_type:

    Row 2: Attachment (image)
    ID: 21895
    post_status: inherit
    post_parent: 21877
    post_type: attachment
    post_mime_type: image/jpeg

    Should be pretty easy I imagine to marry the first image for each post along with the post data in the single query, for the right person at

    least. The query is a bit over my head as-is, much less adding a whole new level to it!

    Thanks so very much, I can't tell you how much I appreciate it.

    Philip

  19. #19
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PS: Rows like that exist in the latest dump.

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aha, i see that it is post_parent that identifies the relationship

    try this --
    Code:
    SELECT wp_posts_3.ID
         , wp_posts_3.post_date
         , wp_posts_3.post_title
         , wp_posts_3.post_content
         , last_image.guid AS last_image_guid
      FROM wp_posts_3
    INNER
      JOIN ( SELECT post_id
                  , MAX(CASE WHEN meta_key = 'ad_city' THEN meta_value END ) AS ad_city
                  , MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END ) AS ad_state
                  , MAX(CASE WHEN meta_key = 'ad_trade_for' THEN meta_value END ) AS ad_trade_for
                  , MAX(CASE WHEN meta_key = 'ad_trade_category' THEN meta_value END ) AS ad_trade_category
                  , MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END ) AS ad_miles_range
                  , MAX(CASE WHEN meta_key = 'ad_zip_code' THEN meta_value END ) AS ad_zip_code
                  , MAX(CASE WHEN meta_key = 'ad_price' THEN meta_value END ) AS ad_price
               FROM wp_postmeta_3 
             GROUP 
                 BY post_id
             HAVING MAX(CASE WHEN meta_key = 'ad_miles_range' THEN meta_value END) = '50'
                AND MAX(CASE WHEN meta_key = 'ad_state' THEN meta_value END) = 'NJ'
           ) AS q1    
        ON q1.post_id = wp_posts_3.ID -- note no parentheses
    LEFT OUTER
      JOIN ( SELECT post_parent
                  , MAX(post_date) AS latest
               FROM wp_posts_3
              WHERE post_status = 'inherit'
                AND post_type = 'attachment'
                AND post_mime_type = 'image/jpeg'
             GROUP
                 BY post_parent
           ) AS q2    
        ON q2.post_parent = wp_posts_3.ID -- note no parentheses
    LEFT OUTER
      JOIN wp_posts_3 AS last_image
        ON last_image.post_parent = wp_posts_3.ID -- note no parentheses
       AND last_image.post_status = 'inherit'
       AND last_image.post_type = 'attachment'
       AND last_image.post_mime_type = 'image/jpeg'
       AND last_image.post_date = q2.latest
     WHERE wp_posts_3.post_status = 'publish'
    ORDER 
        BY wp_posts_3.post_date DESC LIMIT 30
    you can see i've pushed all that MAX business with the HAVING clause to determine eligibility into a subquery, q1, so that the outer query (which now deals with something else as well) doesn't have the GROUP BY on it

    subquery q2 finds the latest image date for each post (using a LEFT OUTER JOIN in case a post doesn't have any images), and then an additional join is needed to retrieve the row (and the guid) that corresponds to that latest date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You rock so hard. Won't you send a link for something from Amazon? We owe you big time.

    Let me know if there is anything at all I can do for you.

    Philip

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words

    i don't expect anything in return, so why not, when the opportunity arises, help someone else on the forums, kind of like paying it forward

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks so much for your help, you've really gone over and above. Maybe it's my good karma for running a WordPress help forum. We've got quite a way to go on this though and time is starting to drag. Would love to hire you for a couple hours to get this thing ironed out if you are interested.

    Anyway, next issue is that I need to pull in all the wp_posts fields in the query, but also it's associated postmeta values. It needs to be all in one row for easy feed to data display system, like...

    ID, post_title, post_content, last_image_guid, ad_city, ad_state, etc.

    Still got to get zip code, city/state by mile range implemented in there too, sheesh.

    Hope you've been well

    Philip


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •