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)

I am not sure what is that you want. Why can't a simple `WHERE` clause solve this? Please give a bit more information about the rules you are looking for. — Martin Dimitrov, Aug 01 '19 at 19:40
I think the simplest thing is looking at the example to see the rules. — Mike, Aug 01 '19 at 19:41
I think even simpler is if you give us some context for the logic you need. Otherwise, we have to use our detective skills and will waste some time. — Juan Carlos Oropeza, Aug 01 '19 at 19:43
Looks like the logic is for each user return the lenguage_id matching the active site language. If doesn't exist, return the first language of that user. Please confirm — Juan Carlos Oropeza, Aug 01 '19 at 19:48

score 1 · Answer 1 · answered Aug 02 '19 at 13:57

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 |

If solve your problem remember mark this answer as correct using the green tick — Juan Carlos Oropeza, Aug 06 '19 at 14:24

score 0 · Answer 2 · answered Aug 01 '19 at 19:39

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;

Felippe Duarte · Answer 3 · 2019-08-01T20:01:53.030

0

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.

edited Aug 01 '19 at 20:01

answered Aug 01 '19 at 19:54

Felippe Duarte

14,901
2
25
29

it will take some time to try it out here. thanks so far. – Mike Aug 01 '19 at 19:56
unknown field 'id' in field list. some error still? – Mike Aug 02 '19 at 09:55

Selecting rows based on a few rules

3 Answers3

Linked