php - Selecting rows based on a few rules

Question

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.

• For every user one row should get selected
• If there is a row with a translation in the active site language it should be prioritized, if there are other rows for that user with different languages
• If there is no row with a translation in the active site language for a user then the oldest translation should get used

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)

Answer 1

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.

SQL DEMO

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 |