英文:
Postgres - Where filter on value or custom default
问题
假设有一个存储给定“locale”下多个“items”的“locale_info”表
```sql
CREATE TABLE locale_info (
locale TEXT,
item_id TEXT
)
对于给定的“search_locale”，或者如果表中没有该“search_locale”的条目，则选择最干净的方法是什么？
我正在使用以下查询：
SELECT *
FROM locale_info
WHERE locale = COALESCE(
(SELECT DISTINCT (locale) FROM locale_info WHERE locale = 'en_ZA'),
'en'
);
如果存在“en_ZA”项，它将返回这些项目，否则将返回默认的“en”语言环境。
我的问题是：如何以更干净的Postgres方式重写这个相当丑陋的查询？
<details>
<summary>英文:</summary>
Assuming a `locale_info` table that stores multiple `items` for a given `locale`
```sql
CREATE TABLE locale_info (
locale TEXT,
item_id TEXT
)
What is the cleanest way to select items for a given search_locale OR a default locale if table has no entries for the given search_locale?
I'm using the following:
SELECT *
FROM locale_info
WHERE locale = COALESCE(
(SELECT DISTINCT (locale) FROM locale_info WHERE locale = 'en_ZA'),
'en'
);
which will return en_ZA items if there are any, else the default en locale.
My question is: How do I rewrite that rather ugly query in a cleaner Postgres fashion?
答案1
得分: 1
以下是您要翻译的代码部分：
一种我可以想到的方法是使用 UNION ALL，只有在第一个返回了一行的情况下才运行第二个部分：
with requested as (
SELECT *
FROM locale_info
WHERE locale = 'en_ZA'
)
select *
from requested
union all
SELECT *
FROM locale_info
where locale = 'en'
and not exists (select * from requested);
另一种选择是始终选择两种区域设置，但按“重要性”对它们进行排序并丢弃第二个：
select *
from locale_info
where locale in ('en_ZA', 'en')
order by locale = 'en_ZA' desc
limit 1;
当对布尔值进行排序时，`false` 在 `true` 之前排序，所以通过按照 DESC 顺序排序，包含所需区域设置的行会首先出现。
无论如何，您应该在 `locale` 列上建立索引（或者在该列是第一列的索引）。
英文:
One way I can think of, is to use a UNION ALL, where the second part is only run if the first returned a row:
with requested as (
SELECT *
FROM locale_info
WHERE locale = 'en_ZA'
)
select *
from requested
union all
SELECT *
FROM locale_info
where locale = 'en'
and not exists (select * from requested);
Another option is to always select both locales but sort them by "importance" and discard the second one:
select *
from locale_info
where locale in ('en_ZA', 'en')
order by locale = 'en_ZA' desc
limit 1;
When sorting booleans, false is sorted before true, so by sorting DESCending, the row that contains the required locale comes first.
In any case, you should have an index on the locale column (or an index where that column is the first column)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论