What is the best way to write SELECT for product translations?

Question

PostgreSQL

there is a table productand product_translate
for one product there are translations in several languages,
we need to select a language that the user understands better (there is one id) if there is no such translation, then we select any other
, this is all in one query
, how best to write?
product_translate:

you can use subqueries, but you will have to write 2 subqueries, 1 for title, 2 for description
and it's still not clear how to write ORDER BYin subqueries

Answer 1

select ...
from product as p
join lateral (
select title, description from product_translate as pt
where pt.product_id = p.id order by language_id = ? limit 1
) on true
where ....

As usual, I could confuse the sorting direction - add desc if there is a translation, but it is not selected.
The idea of ​​choosing any other language in the absence of the right one is strange. But that's exactly what I wrote.
If there are no translations at all, now such a product will not be displayed. If needed with NULL instead of text - replace join with left join.