Rails, Postgres:如何创建一个数据透视表，并将其左连接到另一个表，而无需硬编码列 &g

Question

我在Rails和Postgres工作。我有一个表问题，它有几列。我有另一个表ExtraInfos，其中引用了Problems，有三列:problem_id, info_type, info_value。

例如:

问题:

tbody> <<tr>12

id	problem_type	problem_group
0	type_x	grp_a
	type_y	grp_b
	type_z	grp_c

ActiveRecord是建立在AST查询汇编器Arel之上的。

你可以根据需要使用这个汇编器来构建动态查询，基本上如果你可以将它作为SQL查询手工输入，Arel可以构建它。

在这种情况下，下面的代码将基于文章中提供的表结构构建所需的交叉表查询。

# Get all distinct info_types to build columns
cols = ExtraInfo.distinct.pluck(:info_type)
# extra_info Arel::Table
extra_infos_tbl = ExtraInfo.arel_table
# Arel::Table to use for querying 
tbl = Arel::Table.new('ct')
# SQL data type for the extra_infos.info_type column 
info_type_sql_type = ExtraInfo.columns.find {|c| c.name == 'info_type' }&.sql_type
# Part 1 of crosstab 
qry_txt = extra_infos_tbl.project( 
extra_infos_tbl[:problem_id],
extra_infos_tbl[:info_type],
extra_infos_tbl[:info_value]
) 
# Part 2 of the crosstab  
cats =  extra_infos_tbl.project(extra_infos_tbl[:info_type]).distinct
# construct the ct portion of the crosstab query
ct = Arel::Nodes::NamedFunction.new('ct',[
Arel::Nodes::TableAlias.new(Arel.sql('"problem_id"'), Arel.sql('bigint')),
*cols.map {|name|  Arel::Nodes::TableAlias.new(Arel::Table.new(name), Arel.sql(info_type_sql_type))}
])
# build the crosstab(...) AS ct(...) statement
crosstab = Arel::Nodes::As.new(
Arel::Nodes::NamedFunction.new('crosstab', [Arel.sql("'#{qry_txt.to_sql}'"),
Arel.sql("'#{cats.to_sql}'")]),
ct
)
# final query construction
q = tbl.project(tbl[Arel.star]).from(crosstab)

使用此q.to_sql将产生:

SELECT 
ct.* 
FROM 
crosstab('SELECT 
extra_infos.problem_id, 
extra_infos.info_type, 
extra_infos.info_value 
FROM 
extra_infos', 
'SELECT DISTINCT 
extra_infos.info_type 
FROM 
extra_infos') AS ct(problem_id bigint, 
info_1 varchar(255), 
info_2 varchar(255), 
info_3 varchar(255))

结果是

<表类>problem_idinfo_1info_2info_3tbody><<tr>0v1v2v31v4v5

在postgres中，当列列表可能随时间变化，即列info_type中的值列表可能增加或减少时，透视表或交叉表不相关。

还有一个解决方案是动态创建一个composite type，然后使用标准函数jsonb_build_agg和jsonb_populate_record:

动态创建复合类型column_list:

CREATE OR REPLACE PROCEDURE column_list() LANGUAGE plpgsql AS $$
DECLARE
clist text ;
BEGIN
SELECT string_agg(DISTINCT info_type || ' text', ',')
INTO clist
FROM ExtraInfos ;

EXECUTE 'DROP TYPE IF EXISTS column_list' ;
EXECUTE 'CREATE TYPE column_list AS (' || clist || ')' ;
END ; $$ ;

第一次设置复合类型column_list:

CALL column_list() ;

但是这个复合类型必须在每次更改列ExtraInfos之后更新。这可以通过一个触发器函数来实现:

CREATE OR REPLACE FUNCTION After_Insert_Update_Delete_ExtraInfos () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
CALL column_list() ;
RETURN NULL ;
END ; $$ ;
CREATE OR REPLACE TRIGGER After_Insert_Update_Delete_ExtraInfos AFTER INSERT OR UPDATE OF info_type OR DELETE ON ExtraInfos
FOR EACH STATEMENT EXECUTE FUNCTION After_Insert_Update_Delete_ExtraInfos () ;

最后一个查询是:

SELECT p.id, p. problem_type, p.problem_group, (jsonb_populate_record(NULL :: column_list, jsonb_object_agg(info_type, info_value))).*
FROM Problems AS p
INNER JOIN ExtraInfos AS ei
ON ei.problem_id = p.id
GROUP BY p.id, p. problem_type, p.problem_group

给出结果:

<表类>idproblem_typeproblem_groupinfo_1info_2info_3tbody><<tr>0type_xgrp_av1v2v31type_ygrp_bv4空v5