Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
413 views
in Technique[技术] by (71.8m points)

php - Selecting rows based on a few rules

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)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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 |

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...