如何将多个内连接转换为泛型/可编程函数

Question

假设我们存储文件及其属性(存在几个不同的属性，每个属性都有一个值)：

CREATE TABLE file(
    id UUID NOT NULL,
    name TEXT NOT NULL,
    CONSTRAINT file_primarykey PRIMARY KEY(id)
);

CREATE TABLE attribute(
    id UUID NOT NULL,
    name TEXT NOT NULL,
    CONSTRAINT attribute_primarykey PRIMARY KEY(id)
);

CREATE TABLE file_attribute(
    id UUID NOT NULL,
    file_id UUID NOT NULL,
    attribute_id UUID NOT NULL,
    value TEXT NOT NULL,
    CONSTRAINT file_attribute_primarykey PRIMARY KEY(id),
    CONSTRAINT file_attribute_foreignkey_file_id FOREIGN KEY(file_id) REFERENCES file ON DELETE CASCADE,
    CONSTRAINT file_attribute_foreignkey_attribute_id FOREIGN KEY(attribute_id) REFERENCES attribute ON DELETE CASCADE
);

INSERT INTO file(id, name) VALUES
('aaa2a8e9-a004-44bf-9ec7-0c20733380da', 'Die Verwandlung.pdf'),
('bba2a8e9-a004-44bf-9ec7-0c20733380da', 'Star Wars.pdf');

INSERT INTO attribute(id, name) VALUES
('11a2a8e9-a004-44bf-9ec7-0c20733380da', 'FILE_SIZE'),
('1aa2a8e9-a004-44bf-9ec7-0c20733380da', 'FILE_EXTENSION'),
('2aa2a8e9-a004-44bf-9ec7-0c20733380da', 'FILE_OWNER');

INSERT INTO file_attribute(id, file_id, attribute_id, value) VALUES
('1111a8e9-a004-44bf-9ec7-0c20733380da', 'aaa2a8e9-a004-44bf-9ec7-0c20733380da', '11a2a8e9-a004-44bf-9ec7-0c20733380da', '101'),
('2222a8e9-a004-44bf-9ec7-0c20733380da', 'aaa2a8e9-a004-44bf-9ec7-0c20733380da', '1aa2a8e9-a004-44bf-9ec7-0c20733380da', '.pdf'),
('3333a8e9-a004-44bf-9ec7-0c20733380da', 'aaa2a8e9-a004-44bf-9ec7-0c20733380da', '2aa2a8e9-a004-44bf-9ec7-0c20733380da', 'James'),
('4444a8e9-a004-44bf-9ec7-0c20733380da', 'bba2a8e9-a004-44bf-9ec7-0c20733380da', '11a2a8e9-a004-44bf-9ec7-0c20733380da', '251'),
('5555a8e9-a004-44bf-9ec7-0c20733380da', 'bba2a8e9-a004-44bf-9ec7-0c20733380da', '1aa2a8e9-a004-44bf-9ec7-0c20733380da', '.pdf');

情形:我想通过文件的属性来获取文件：

-- Search for all files that have a file attribute "FILE_EXTENSION" ending with .pdf --> Will return Die Verwandlung
SELECT f.id, f.name FROM file f INNER JOIN file_attribute fa ON f.id = fa.file_id WHERE fa.attribute_id = '1aa2a8e9-a004-44bf-9ec7-0c20733380da' AND fa.value = '.pdf';

-- Search for all files that have a file attribute "FILE_SIZE" with the size of 251 -->  Will return Star Wars
SELECT f.id, f.name FROM file f INNER JOIN file_attribute fa ON f.id = fa.file_id WHERE fa.attribute_id = '11a2a8e9-a004-44bf-9ec7-0c20733380da' AND fa.value = '251';

-- Search for file extension .pdf and file size 101 --> Will return Die Verwandlung
SELECT f.id, f.name FROM file f
INNER JOIN file_attribute fa1 ON f.id = fa1.file_id
INNER JOIN file_attribute fa2 ON f.id = fa2.file_id 
WHERE fa1.attribute_id = '1aa2a8e9-a004-44bf-9ec7-0c20733380da' AND fa1.value = '.pdf' AND fa2.attribute_id = '11a2a8e9-a004-44bf-9ec7-0c20733380da' AND fa2.value = '101';

问题/问题：

1.)dow multiple joins如何影响同一个joins表的性能？对于过早优化有什么建议吗？或者有没有更好的方法来设计查询？

2.)如何将查询转换为自定义函数，以便以地图样式的方式向下传递attribute_ids和value？这个想法是在文件中搜索0-n个文件属性+文件中不属于这个问题的其他列。伪调用：

select search_files([{attribute_id: 1aa2a8e9-a004-44bf-9ec7-0c20733380da, value: .pdf}, {attribute_id: 11a2a8e9-a004-44bf-9ec7-0c20733380da, value: 101}])

我想从JDBC调用此函数，并希望将值传递给Map (如果可能的话)

Answer 1

如果您不需要结果rs中的属性，则where子句中的Exists可能更好

在函数中添加这个没有问题吗？

SELECT f.id, f.name FROM file f 
WHERE EXISTS (select 0 from file_attribute fa1 ON f.id = fa1.file_id and fa1.attribute_id = '1aa2a8e9-a004-44bf-9ec7-0c20733380da' AND fa1.value = '.pdf') AND 
EXISTS (select 0 from file_attribute fa2 ON f.id = fa2.file_id and fa2.attribute_id = '1aa2a8e9-a004-44bf-9ec7-0c20733380da' AND fa2.value = '101')