OK, curious to see if there is a meaningful solution for this. I'm having only one table, from which I need to get rows from every user according to the current active site language.
I think it's easier to understand looking at some examples I made, instead of me trying to explain it.
I could solve this in PHP filtering stuff out but if there is a query that's not too crazy, I would prefer it.
Of course the query should use the current site language stored in $l.
If someone has a better idea for a title of this question please change :)
id, title, language, created, user
1, a, en, 2019-01-01, 4
2, b, es, 2019-01-02, 4
3, c, de, 2019-01-03, 4
$l = 'de' -- active site language
-> 3 -- getting 'de' translation of user 4 (only user here)
id, title, language, created, user
1, a, en, 2019-01-01, 4
2, b, es, 2019-01-02, 4
$l = 'de' -- active site language
-> 1 -- getting 'en' because no translation for active lang AND en was frist created from user 4
id, title, language, created, user
1, a, en, 2019-01-01, 3
2, b, es, 2019-01-02, 4
3, b, de, 2019-01-03, 4
4, c, de, 2019-01-04, 5
$l = 'de' -- active site language
-> 1,3,4 -- getting de translation of users 4 and 5 AND 'en' translation of user 3 because it's his only
id, title, language, created, user
1, a, en, 2019-01-01, 3
2, b, es, 2019-01-02, 4
3, b, de, 2019-01-03, 4
4, c, de, 2019-01-04, 5
$l = 'es' -- active site language
-> 2,1,4 -- getting es translation of user 4 AND en translation of user 3 AND de translation of user 5
id, title, language, created, user
1, a, en, 2019-01-01, 3
2, b, es, 2019-01-02, 4
3, b, de, 2019-01-03, 4
4, c, de, 2019-01-04, 5
5, d, en, 2019-01-02, 5
$l = 'es' -- active site language
-> 2,1,5 -- getting es translation of user 4 AND en translation of user 3 AND en translation of user 5 (es was created before de from user 5)
You seem to want a prioritization per user, based on the language. The simplest method uses row_number():
select t.*
from (select t.*,
row_number() over (partition by user order by (language = ?) desc, created) as seqnum
from t
) t
where seqnum = 1;
If I understood what you need, you need to get the id where the language is 'de', otherwise the first id overall
select case when sum(id_language) > 0
then sum(id_language)
else sum(id_first_language)
end as id,
user,
language
from (
select max(id) as id_language, 0 as id_first_language, user, language
from table
where language = 'de'
group by user, language
union all
select 0 as id_language, min(id) as id_first_language, user, language
from table
where language <> 'de'
group by user, language
) t
group by user, language
Not sure if this has a good performance, but should solve.
In this demo I put together all test cases on the same table. Here you can check the right result for cases 1,2,3 for case 4,5 you need change @language := 'de' to 'es'.
For your final version won't need @partition, only @user.
Basically this is the same solution as @Gordon but because you can't use row_number() we emulate it using user variables.
SELECT *
FROM (
SELECT t.*,
@rn := if (@partition = CONCAT(`test_id`, '-', `user`),
@rn + 1,
if(@partition := CONCAT(`test_id`, '-', `user`), 1, 1)
) as rn,
@partition
FROM (
SELECT *, (language = @language) AS priority
FROM Table1
CROSS JOIN (SELECT @language := 'de' as site_lang) AS var
ORDER BY CONCAT(test_id, '-', user),
priority DESC,
created
) AS t
CROSS JOIN ( SELECT @rn := 0, @partition := '' ) as var
) r
WHERE r.rn = 1;
OUTPUT
using @language := 'de' for first 3 test cases.
| test_id | id | title | language | created | user | site_lang | priority | rn | @partition |
|---------|----|-------|----------|----------------------|------|-----------|----------|----|------------|
| 1 | 3 | c | de | 2019-01-03T00:00:00Z | 4 | de | 1 | 1 | 1-4 |
| 2 | 1 | a | en | 2019-01-01T00:00:00Z | 4 | de | 0 | 1 | 2-4 |
| 3 | 1 | a | en | 2019-01-01T00:00:00Z | 3 | de | 0 | 1 | 3-3 |
| 3 | 3 | b | de | 2019-01-03T00:00:00Z | 4 | de | 1 | 1 | 3-4 |
| 3 | 4 | c | de | 2019-01-04T00:00:00Z | 5 | de | 1 | 1 | 3-5 |