Tworzę instrukcję select, która łączy dwie tabele,zone ioutput, w oparciu o odniesieniedevice tabela i mapowaniezone_number dooutput_type_id. Mapowaniezone_number dooutput_type_id nie pojawia się nigdzie w bazie danych i chciałbym ją utworzyć „w locie” w instrukcji select. Poniżej znajduje się mój schemat:

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)
);

A oto kilka przykładowych danych:

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);

Muszę uzyskać skojarzenieenabled pole z tabeli wyjściowej dla każdej strefy dla danego urządzenia. Każdyzone_number mapuje naoutput_type_id. W tym przykładzie:

zone_number | output_type_id
----------------------------
1           | 101
2           | 202
3           | 303 
4           | 404

Jednym ze sposobów radzenia sobie z mapowaniem byłoby utworzenie nowej tabeli

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);

Użyj poniższego kodu SQL, aby pobrać wszystkie strefy, a takżeenabled flag, dla urządzenia 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

Szukam jednak sposobu na utworzenie odwzorowania węzłów strefowych na typy wyjściowe bez tworzenia nowej tabeli i bez łączenia kilku instrukcji AND / OR. Czy istnieje elegancki sposób tworzenia odwzorowania między dwoma polami w instrukcji select? Coś jak:

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

Zastrzeżenie: Wiem, że najlepiejenabled pole istniałoby wzone stół. Nie mam jednak kontroli nad tym utworem. Po prostu szukam najbardziej eleganckiego rozwiązania ze strony aplikacji. Dzięki!