How to replace not found rows with custom query?

Question

I have an application in which every news, there are translations into your language. But the problem is that there may not be a translation into a user language, and in this case, I need to choose a "standard (any)" language, but I'm not really good at it yet.

Data:

create table languages(id int, name varchar(255), created_at timestamp);
create table posts(id int, created_at timestamp);
create table post_translations(
id int, post_id int,
language_id int, name varchar(255),
is_default boolean, is_approved boolean,
created_at timestamp);


insert into languages(id, name, created_at) values
('1', 'Russian', '2018-12-28 22:46:35'),
('2', 'English', '2018-12-28 22:46:35');

insert into posts(id, created_at) values
('1', '2018-12-28 22:46:35'),
('2', '2018-12-28 22:46:35');

insert into post_translations(id, post_id, language_id, name, is_default, is_approved) values
('1', '1', '1', 'Russian name - first post', true,true),
('1', '1', '1', 'Russian not moderated name - first post', false,false),
('1', '1', '2', 'English name - first post', false,true),
('1', '2', '2', 'English name - second post',true,true),
('1', '2', '2', 'Russian not moderated name - second post', false,false);

Query:

WITH r AS (
SELECT * FROM post_translations
  where is_approved = true and language_id = 1
  and post_id in (1,2)
)
SELECT * FROM r
UNION ALL
SELECT * FROM post_translations
  where is_approved = true
  and post_id in (1,2)
  AND NOT EXISTS (
SELECT * FROM post_translations 
   where is_approved = true and language_id = 1
   and post_id in (1,2)
);

DbFiddle.

Here I try to take translations in Russian for two news, and take a standard ( English ) translation, if Russian is not found, but does not come out...

Please show the results that you want. How do you know the user's language? — Gordon Linoff
– Gordon Linoff, Commented Jan 20, 2019 at 11:53

Gordon Linoff · Accepted Answer · 2019-01-20 11:55:45Z

1

If I understand correctly, distinct on does what you want:

select distinct on (pt.post_id) pt.*
from post_translations pt
where pt.post_id in (1, 2)
order by pt.post_id,
         (case when language_id = 2 then 1
               when language_id = 1 then 2
               else 3
          end);

This returns one row for each post_id, along with the appropriate language prioritized by the case expression.

answered Jan 20, 2019 at 11:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Uebanchik Over a year ago

Thanks for answer, but a little bit, not what i need. About last comment: In a server side it will be just GET parameter, like ?language_id=1, that's how, I know what language to take. Let's say your language is Russian - I need to choose all translations in Russian, and if there is no translation in Russian, i need to take any other.

Uebanchik Over a year ago

I changed a little bit Your sql, and the like, this is what you need. I'll test it and let you know!

Uebanchik Over a year ago

Yeah, that sounds like it. Thank you!

Collectives™ on Stack Overflow

How to replace not found rows with custom query?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related