Cómo crear una tabla de asignación "al vuelo" dentro de una declaración SELECT en Postgresql
Estoy creando una declaración de selección que combina dos tablas,zone
youtput
, basado en una referenciadevice
mesa y en un mapeo dezone_number
aoutput_type_id
. El mapeo dezone_number
aoutput_type_id
no aparece en ninguna parte de la base de datos, y me gustaría crearla "sobre la marcha" dentro de la declaración de selección. A continuación se muestra mi esquema:
CREATE TABLE output_type (
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY (id)
);
CREATE TABLE device (
id INTEGER NOT NULL,
name TEXT,
PRIMARY KEY (id)
);
CREATE TABLE zone (
id SERIAL NOT NULL,
device_id INTEGER NOT NULL REFERENCES device(id),
zone_number INTEGER NOT NULL,
PRIMARY KEY (id),
UNIQUE (zone_number)
);
CREATE TABLE output (
id SERIAL NOT NULL,
device_id INTEGER NOT NULL REFERENCES device(id),
output_type_id INTEGER NOT NULL REFERENCES output_type(id),
enabled BOOLEAN NOT NULL,
PRIMARY KEY (id)
);
Y aquí hay algunos datos de ejemplo:
INSERT INTO output_type (id, name) VALUES
(101, 'Output 1'),
(202, 'Output 2'),
(303, 'Output 3'),
(404, 'Output 4');
INSERT INTO device (id, name) VALUES
(1, 'Test Device');
INSERT INTO zone (device_id, zone_number) VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4);
INSERT INTO output (device_id, output_type_id, enabled) VALUES
(1, 101, TRUE),
(1, 202, FALSE),
(1, 303, FALSE),
(1, 404, TRUE);
Necesito conseguir lo asociadoenabled
campo de la tabla de salida para cada zona para un dispositivo dado. Cadazone_number
mapas a unoutput_type_id
. Para este ejemplo:
zone_number | output_type_id
----------------------------
1 | 101
2 | 202
3 | 303
4 | 404
Una forma de manejar la asignación sería crear una nueva tabla
CREATE TABLE zone_output_type_map (
zone_number INTEGER,
output_type_id INTEGER NOT NULL REFERENCES output_type(id)
);
INSERT INTO zone_output_type_map (zone_number, output_type_id) VALUES
(1, 101),
(2, 202),
(3, 303),
(4, 404);
Y use el siguiente SQL para obtener todas las zonas, más laenabled
bandera, para el dispositivo 1:
SELECT zone.*, output.enabled
FROM zone
JOIN output
ON output.device_id = zone.device_id
JOIN zone_output_type_map map
ON map.zone_number = zone.zone_number
AND map.output_type_id = output.output_type_id
AND zone.device_id = 1
Sin embargo, estoy buscando una manera de crear la asignación de nunbers de zona a tipos de salida sin crear una nueva tabla y sin juntar un montón de declaraciones AND / OR. ¿Existe una forma elegante de crear una asignación entre los dos campos dentro de la declaración de selección? Algo como:
SELECT zone.*, output.enabled
FROM zone
JOIN output
ON output.device_id = zone.device_id
JOIN (
SELECT (
1 => 101,
2 => 202,
3 => 303,
4 => 404
) (zone_number, output_type_id)
) as map
ON map.zone_number = zone.zone_number
AND map.output_type_id = output.output_type_id
AND zone.device_id = 1
Descargo de responsabilidad: Sé que idealmente laenabled
campo existiría en elzone
mesa. Sin embargo, no tengo control sobre esa pieza. Solo estoy buscando la solución más elegante desde el lado de la aplicación. ¡Gracias!