Multiple LEFT JOIN's in Wordpress plugin

Databases
1

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:



[B]_ wpds_postmeta _____[/B]
[B]
post_id     meta_key             meta_value
[/B]
37          page_description     This is the description


[B]_ wpds_posts _____[/B]
[B]
ID     post_title     guid
[/B]
37     The title      http://example-url.com/example/


[B]_ wpds_releases _____[/B]
[B]
release_id     post_id     region_id     platform_id     release_date         release_url                   release_info
[/B]
11             37          2             3               2014-11-20 14:00     http://example.com/store/     Information about the release


[B]_ wpds_platforms _____[/B]
[B]
platform_id     platform_name     platform_short
[/B]
3               PlayStation 4     PS4


[B]_ wpds_regions _____[/B]
[B]
region_id     region_name     region_short
[/B]
2             Europe          EU

My query is the following:


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[COLOR="#808080"]/*,
wpds_postmeta.meta_value AS post_description*/[/COLOR]
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
[COLOR="#808080"]/*LEFT JOIN wpds_postmeta ON wpds_postmeta.post_id = wpds_posts.ID AND wpds_postmeta.meta_key = "page_description"*/[/COLOR]
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 :wink: ).

Greetings,
xtaste

2

“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”

3

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

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

5

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

6

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 :slight_smile:

7

oh yes :slight_smile: