xtaste — 2013-12-20T09:05:14-05:00 — #1
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]
post_id meta_key meta_value
37 page_description This is the description
[B]_ wpds_posts _____[/B]
ID post_title guid
37 The title http://example-url.com/example/
[B]_ wpds_releases _____[/B]
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
[B]_ wpds_platforms _____[/B]
platform_id platform_name platform_short
3 PlayStation 4 PS4
[B]_ wpds_regions _____[/B]
region_id region_name region_short
2 Europe EU
My query is the following:
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<font color='"#808080"'>/*,
wpds_postmeta.meta_value AS post_description*/</font>
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
<font color='"#808080"'>/*LEFT JOIN wpds_postmeta ON wpds_postmeta.post_id = wpds_posts.ID AND wpds_postmeta.meta_key = "page_description"*/</font>
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 ).
r937 — 2013-12-20T11:21:55-05:00 — #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"
xtaste — 2013-12-20T12:42:54-05:00 — #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?
r937 — 2013-12-20T13:07:13-05:00 — #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
r937 — 2013-12-20T13:07:54-05:00 — #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
xtaste — 2013-12-20T14:06:56-05:00 — #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
r937 — 2013-12-20T18:50:47-05:00 — #7