CODEWITHSUNDEEP
mysql
Red October
Red October

Reputation: 689

Table with multi language news

I have a table with news, looks like this:

enter image description here

As you can see each of the news have a language (news_lang): there will be an English version (en), and optionally the same news, but in a different language (ru, de, fr - doesn't matter). Same news combined by the field news_root_id (eg, en and ru version of the same news will have the same news_root_id). At any given time, I know which interface language the user has selected.

So my question is: is it possible to somehow to select a news feed with such conditions

Upvotes: 2

Views: 119

Answers (1)

forpas
forpas

Reputation: 164099

With UNION ALL to cover both cases.
I use a variable holding the user's language selection:

set @lang = 'en'; -- or 'ru'

select * from tablename
where news_lang = @lang
union all
select t.* from tablename t
where @lang <> 'en' and t.news_lang = 'en'
and not exists (
  select 1 from tablename
  where news_root_id = t.news_root_id and news_lang = @lang
);

And a solution for MySQL 8.0+ with ROW_NUMBER() window function:

set @lang = 'en'; 

select t.id, t.news_created_datetime, t.news_root_id, t.news_lang, t.news_image 
from (
  select *,
    row_number() over (partition by news_root_id order by (news_lang <> @lang), (news_lang <> 'en')) rn
  from tablename
) t
where t.rn = 1

See a simplified demo.

Upvotes: 1

Related Questions