将 SQL 中具有重复列名的表联接为点表示法，而无需使用别名列出每一列

Question

我猜这个问题经常出现，是我几个失误和设计缺陷的结果，但我现在有点卡住了，我不知道去哪里看，因为我太像一个SQL新手。

事情的基本要点是我开始使用 Sequelize 构建我的应用程序，它有一个include函数，本质上将表连接为嵌套对象。但是现在 Sequelize 对我不起作用，我需要做原始查询，但我仍然有依赖于 Sequelize 会给出的结构的应用程序代码。基本上我需要重新创建续集包含功能，但不要那么冗长。

因此，在我进入细节之前总结一下环境：PostgreSQL 和 JavaScript with Sequelize & Knex(用于帮助以编程方式构建查询)。

所以我有表foo

id | name | bar_id
---+------+-------
1  | Joe  | 1
2  | Jan  | 2

表bar

id | pet | vet_id
---+-----+-------
1  | cat | 1
2  | dog | 1

表vet

id | name
---+-----
1  | Dr. Elsey

理想的结果如下所示：

id | name | bar.id | bar.pet | bar.vet.id | bar.vet.name
---+------+--------+---------+------------+-------------
1  | Joe  | 1      | cat     | 1          | Dr. Elsey
2  | Jan  | 2      | dog     | 1          | Dr. Elsey

Sequelize 通过做这样的事情来实现这一点(释义选择)：

select
foo.id,
foo.name,
bar.id as "bar.id",
bar.pet as "bar.pet",
"bar->vet".id as "bar.vet.id",
"bar->vet".name as "bar.vet.name"
from foo
left outer join bar on foo.bar_id = bar.id
left outer join vet as "bar->vet" on bar.vet_id = vet.id;

有没有办法在不枚举所有这些选择别名的情况下做到这一点？或者我可以尝试一些更好的输出吗？

本质上，我想构建这样的对象：

{
id: 1,
name: 'Joe',
bar: {
id: 1,
pet: 'cat',
vet: {
id: 1,
name: 'Dr. Elsey'
}
}
};

Answer 1

我不会将其归类为更容易或一定更好 - 它肯定更复杂 - 但如果你真的想避免别名，你可以尝试一些SQL元编程使用Postgres的内置information_schema.columns表为你生成动态SQL。

与此类似的内容适合您的确切需求：

select ' select json_agg(x) from (select '
|| array_to_string(array_agg(table_schema
|| '.' || table_name || '.'
|| column_name || ' as "' || table_schema || '.' || table_name || '.'
|| column_name || '"'), ', ') ||
' from public.foo join public.bar using(id)) as x'
from information_schema.columns 
where table_schema = 'public'
and table_name in ('foo', 'bar');

上述查询会将每一行作为 JSON blob 返回，其中包含完全架构限定的列。您可以使用Postgres的其他内置JSON函数来修改它以满足您的需求。

注意：在构造此类查询时，请务必注意SQL 注入的潜力。也就是说，如果最终将脚本中的其他变量插入到此动态生成的 SQL 中，请非常小心。如果您发现自己需要这样做，您可能希望将动态 SQL 包装在您创建的纯 SQL 函数中，该函数使用绑定变量并将其作为参数，如 Bobby Tables 站点中所述。