so I have been struggling with this one for a while. While I could indeed just make two separate queries for this problem, I wonder if it would be possible to do one query. I think a SQL pro will certainly know how to get this done. So here is the thing:
We have two tables posts and post_translations. Abbreviated for simplicity.
posts
------
id
post_translations
-----------------
id
post_id (which is the FK on posts...)
locale
slug
content
... (and so on)
It's clear for me that I could now do a very simple INNER JOIN to get all posts translated in a specific language, let's say 'en' or 'de' if you want. So I am not gonna bother you further with this.
But as the table will also hold sub locales, such as en (for USA), en-GB, en-AU, de (for Germany), de-AT, de-CH .... the whole thing becomes a bit more complex for following szenario.
Let's say there are posts translated in the languages 'de' only and also in 'de-AT'. The table would then look like:
post_translations
id post_id locale content
1 1 de ...
2 1 de-AT ...
3 2 de ...
So post 1 is available in 'de' and 'de-AT'. Post 2 is only available in 'de-AT'.
If I want to have all posts in 'de' that's easy. I just add a WHERE locale = 'de' and I am good. But let's say I want all posts in 'de-AT' and all other posts in 'de' that are not translated in 'de-AT' so I don't get any duplicates - how can I achieve that in one query? As mentioned earlier, I could run two queries here, first I get all the posts in 'de-AT', then I get all the posts in 'de' and use the 'post_id's I got from the first query with a WHERE not IN query, so I don't get any duplicates.
These queries would be then:
SELECT
pt.post_id
FROM
posts AS p
INNER JOIN
post_translations AS pt ON p.id = pt.post_id
WHERE
pt.locale = 'de-AT';
and from this query you would use the post_id in this one:
SELECT
*
FROM
posts AS p
INNER JOIN
post_translations AS pt ON p.id = pt.post_id
WHERE
pt.locale = 'de' AND pt.post_id NOT IN (*post_ids found in first search*);
So staying with the above mentioned post_translations table the desired result would be:
p.id pt.id pt.post_id pt.locale pt.content
1 2 1 de-AT ...
2 3 2 de ...
p stands for posts and pt for post_translations obviously.
The idea behind the query is to show the specific posts for a region, in this case 'de-AT' but also to show the generic posts that were written for 'de' users.
I hope that makes sense. Would appreciate any help on this. Thank You.
One option uses not exists:
select pt.*
from post_translations pt
where
locale = 'de-AT'
or (
locale = 'de'
and not exists (
select 1
from post_translations pt1
where pt1.post_id = pt.post_id and pt1.locale = 'de-AT'
)
)
Alternatively, if you are running MySQL 8.0, you can also use row_number():
select *
from (
select
pt.*,
row_number() over(partition by post_id order by (locale = 'de')) rn
from post_translations pt
where locale in ('de', 'de-AT')
) pt
where rn = 1
You can easily modify the above queries to join the posts table.
show the specific posts for a region, in this case 'de-AT' but also to show the generic posts that were written for 'de' users
I believe that a simple WHERE clause with the operator IN would return your expected results.
Then you can use conditional aggregation to flag the posts that have the sub locale that you want and maybe sort these posts first:
SELECT post_id,
MAX(locale = 'de-AT') AS flag
FROM post_translations
WHERE locale IN ('de', 'de-AT')
GROUP BY post_id
ORDER BY flag DESC
You can join the above query to posts to get the details of each post:
SELECT p.*, t.flag
FROM posts AS p INNER JOIN (
SELECT post_id,
MAX(locale = 'de-AT') AS flag
FROM post_translations
WHERE locale IN ('de', 'de-AT')
GROUP BY post_id
) t ON t.post_id = p.id
ORDER BY t.flag DESC