SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple LEFT JOIN's in Wordpress plugin

    Hello all,

    I'm having trouble with writing a SELECT query. It seemed to go well, but after joining a last LEFT JOIN I'm getting unexpected results.

    My (and Wordpress') tables are as following:

    Code:
    _ wpds_postmeta _____
    
    post_id     meta_key             meta_value
    
    37          page_description     This is the description
    
    
    _ wpds_posts _____
    
    ID     post_title     guid
    
    37     The title      http://example-url.com/example/
    
    
    _ wpds_releases _____
    
    release_id     post_id     region_id     platform_id     release_date         release_url                   release_info
    
    11             37          2             3               2014-11-20 14:00     http://example.com/store/     Information about the release
    
    
    _ wpds_platforms _____
    
    platform_id     platform_name     platform_short
    
    3               PlayStation 4     PS4
    
    
    _ wpds_regions _____
    
    region_id     region_name     region_short
    
    2             Europe          EU
    My query is the following:

    Code:
    SELECT
    GROUP_CONCAT(wpds_releases.release_date SEPARATOR "|") AS release_date,
    GROUP_CONCAT(wpds_releases.release_url SEPARATOR "|") AS release_url,
    GROUP_CONCAT(wpds_releases.release_info SEPARATOR "|") AS release_info,
    GROUP_CONCAT(wpds_platforms.platform_name SEPARATOR "|") AS platform_name,
    GROUP_CONCAT(CASE WHEN wpds_platforms.platform_short = "" THEN wpds_platforms.platform_name ELSE wpds_platforms.platform_short END SEPARATOR "|") AS platform_short,
    GROUP_CONCAT(wpds_regions.region_name SEPARATOR "|") AS region_name,
    GROUP_CONCAT(CASE WHEN wpds_regions.region_short = "" THEN wpds_regions.region_name ELSE wpds_regions.region_short END SEPARATOR "|") AS region_short,
    wpds_posts.post_title AS post_title,
    wpds_posts.guid AS post_url/*,
    wpds_postmeta.meta_value AS post_description*/
    FROM wpds_releases
    LEFT JOIN wpds_platforms ON wpds_platforms.platform_id = wpds_releases.platform_id
    LEFT JOIN wpds_regions ON wpds_regions.region_id = wpds_releases.region_id
    LEFT JOIN wpds_posts ON wpds_posts.ID = wpds_releases.post_id
    /*LEFT JOIN wpds_postmeta ON wpds_postmeta.post_id = wpds_posts.ID AND wpds_postmeta.meta_key = "page_description"*/
    GROUP BY wpds_posts.ID
    ORDER BY wpds_posts.post_title, wpds_regions.region_name, wpds_platforms.platform_name ASC
    With the lines commented, there's no problem and the query runs fast and accurate. Without the comment tags however, the query returns quite some unexpected results. (Also without the "AND", which I'm not 100% sure about, in the last (commented) LEFT JOIN. With unexpected results I mean it seems like it's doing a non-distinctive GROUP_CONCAT, but without values and only the "|" seperators.

    Please help me out, I'm so close to finishing this plugin (at least, I think so ).

    Greetings,
    xtaste

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    "it seems like it's doing a non-distinctive GROUP_CONCAT" doesn't make sense to me, because joining the postmeta table doesn't affect any GROUP_CONCAT

    also, why the GROUP BY ??? other than GROUP_CONCAT you aren't doing any aggregation

    however, aggregation takes place over all returned rows, so perhaps there are multiple page descriptions per post that is screwing up your query?

    one way to debug a GROUP BY query is to remove the GROUP BY clause (and fix any aggregate functions involved) and then inspect the un-grouped results to determine if, or in this case, more probably why, there are "duplications"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While typing my reply I restarted my SQL server (just out of a "this really has to work"-mindset) and voila my previous code works... I've not changed anything, copied the code from my first post. That's kinda weird? Anyway, it's solved. The code used above works for anyone who can use that code.

    On the GROUP BY and GROUP_CONCAT; of course I'm dealing with multiple items and multiple releases per item otherwise there wouldn't be need for them. Or have I not understood your point?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    items? releases per item?

    probably not worth going into, but i have a hard time reconciling that with your quer,y which deals with posts, regions, and platforms... and platform releases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    items? releases per item?

    probably not worth going into, but i have a hard time reconciling that with your query, which deals with posts, regions, and platforms... and platform releases
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, for example; You've got two games named "A" and "B".

    A has got two release dates; one in Europe and one in the US on PS4, but on the Xbox One it's coming out later and only in the US, so in total 3 releases in 2 countries on two consoles.

    B has some other release dates on other platforms like Nintendo Wii and Nintendo 3DS and only in JPN first, but later also in the EU.

    and so on...

    On the website there's a page with multiple subpages that represent the games. On the parent page there's a collection of games that have show their supported platforms and release dates per region, along with a description, stored in the meta fields. Only games with a release date are shown here so work in progress-games are not shown. I hope you've got enough information now

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by xtaste View Post
    I hope you've got enough information now
    oh yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

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
  •