SQL, order by article language with priorities for English then French

Question

I'm working on a muli language website and I have articles they may or may not be in all the languages the website is in. Also the user has the possibility to register and choose a preferred language.

My problem is the following, when displaying an article I would like for it to display it in this way : if the user is logged in then display the language he has chosen, if an article is not in his language try and display it in English else if it doesn't exist French, then German etc.

This is my article table :

news_id |  lang  |   title  |  text  |
      1 |    en  |  News en |  bla.. |
      1 |    fr  |  News fr |  bla.. |
      2 |    en  |   New en |  bla.. |
      3 |    fr  |   Nws fr |  bla.. |

I have no idea how to do this in mysql.

And I hope you can understand my problem, I don't really know how to explain it more clearly.

Answer 1

You need to join in to the user preferences table, to get the desired language. And then use that information in the order by:

select a.*
from articles a cross join
     (select up.*
      from UserPreferences up
      where up.userid = <whatever you want>
     ) up
order by (case when a.lang = up.prefland then 0
               when a.lang = 'en' then 1
               when a.lang = 'fr' then 2
               else 3 end) asc

This particular order by does the preferred language first, then English, then French. To just get the preferred first, remove the second two "when" clauses.