Select with own array. PostgreSQL 9.5

Question

I have the following schema:

create table reports (id bigserial primary key);
create table scens (id bigserial primary key,report_id bigint references reports(id), path_id bigint);
create table runs (id bigserial primary key,scen_id bigint references scens(id));
create table fails (
    id bigserial primary key,
    run_id bigint references runs(id),
    type varchar not null
);

create table errors (
    id bigserial primary key,
    run_id bigint references runs(id),
    type varchar not null
);
INSERT INTO reports(id)
    VALUES (1);
INSERT INTO reports(id)
    VALUES (2);

INSERT INTO scens(id, report_id)
    VALUES (555, 1);
INSERT INTO scens(id, report_id)
    VALUES (666, 2);

INSERT INTO runs(id, scen_id)
    VALUES (1, 555);
INSERT INTO runs(id, scen_id)
    VALUES (2, 666);
INSERT INTO runs(id, scen_id)
    VALUES (3, 666);
INSERT INTO runs(id, scen_id)
    VALUES (4, 666);

INSERT INTO errors(id, run_id, type)
    VALUES (DEFAULT, 2, 'ERROR2 TYPE');
INSERT INTO errors(id, run_id, type)
    VALUES (DEFAULT, 3, 'ERROR2 TYPE');

INSERT INTO fails(id, run_id, type)
    VALUES (DEFAULT, 1, 'Attachment Journal Mismatch');
INSERT INTO fails(id, run_id, type)
    VALUES (DEFAULT, 2, 'Appeared new difficulty');
INSERT INTO fails(id, run_id, type)
    VALUES (DEFAULT, 2, 'Parameters Mismatch');
INSERT INTO fails(id, run_id, type)
    VALUES (DEFAULT, 3, 'Attachment Journal Mismatch');
INSERT INTO fails(id, run_id, type)
    VALUES (DEFAULT, 3, 'Appeared new difficulty');
INSERT INTO fails(id, run_id, type)
    VALUES (DEFAULT, 3, 'Parameters Mismatch');

I need to find a run (actually more runs) which have errors ERROR2 TYPE and FAILS 'Appeared new difficulty' and 'Parameters Mismatch'.

Correct

1 -> 'ERROR2 TYPE'
1 -> 'Appeared new difficulty'
1 -> 'Parameters Mismatch'

Incorrect

2 -> 'Parameters Mismatch'
2 -> 'Appeared new difficulty'

So, the request should find all runs where requested fails and errors belong to a particular run:

The 1 run doesn't suit me since it doesn't have 'Parameters Mismatch' and doesn't have the error ERROR2 TYPE. The 2 run suits me since it has all errors (ERROR2 TYPE) and all fails ('Appeared new difficulty' and 'Parameters Mismatch'). The 3 run suits me since it has all errors (ERROR2 TYPE) and all fails ('Appeared new difficulty' and 'Parameters Mismatch'). It's ok if the run has more fails (it has the new fail: 'Attachment Journal Mismatch') than we requested but it must have at least requested fails and errors.

How can I do it?

The following sql doesn't work at all (it finds nothing):

select scens.id as scen_id,
scens.path_id, fails.type, reports.id, runs.id
from reports
inner join scens on reports.id=scens.report_id
inner join runs on runs.scen_id=scens.id
inner join fails on fails.runs_id=runs.id
where reports.id=2 and fails.type=''Parameters Mismatch' 
and fails.type='Appeared new difficulty'

This code soens;t work too, it search only 1 match but not the whole set of ('Appeared new difficulty', 'Parameters Mismatch'):

select scens.custom_id as scen_custom_id, scens.id as scen_id,
    scens.path_id, scens.category, fails_map.type, f.error_type
    from reports
    inner join scens on reports.id=scens.report_id
    inner join runs on runs.scen_id=scens.id
    inner join fails on fails.runs_id=runs.id
    INNER JOIN 
    unnest(array['Appeared new difficulty', 'Parameters Mismatch']) f (error_type) 
    on fails.type=f.error_type
    where reports.id=2

Seems I need some kind of an intersection.

score 0 · Answer 1 · answered Feb 17 '17 at 13:39

This is my understanding:

select
    reports.id as report,
    scens.id as scen,
    scens.path_id as path,
    runs.id as run,
    array_agg(distinct fails.type) as fails,
    array_agg(distinct errors.type) as errors
from
    reports
    inner join
    scens on reports.id = scens.report_id
    inner join
    runs on runs.scen_id = scens.id
    left join
    fails on fails.run_id = runs.id
    left join
    errors on errors.run_id = runs.id
where reports.id = 2
group by 1,2,3,4
having
    array['Parameters Mismatch','Appeared new difficulty']::varchar[] <@ array_agg(fails.type) and
    array['ERROR2 TYPE']::varchar[] <@ array_agg(errors.type)
;
 report | scen | path | run |                                      fails                                      |     errors      
--------+------+------+-----+---------------------------------------------------------------------------------+-----------------
      2 |  666 |      |   2 | {"Appeared new difficulty","Parameters Mismatch"}                               | {"ERROR2 TYPE"}
      2 |  666 |      |   3 | {"Appeared new difficulty","Attachment Journal Mismatch","Parameters Mismatch"} | {"ERROR2 TYPE"}

score 0 · Accepted Answer · answered Feb 17 '17 at 15:00

I have solved it with the sql:

select runs.id
    from reports
    inner join scens s on reports.id=s.report_id
    inner join runs on runs.scen_id=s.id
    where exists(select * from fails where fails.path_id=s.path_id and fails.type='Rotation Mismatch')
    and exists(select * from fails where fails.path_id=s.path_id and fails.type='Disappeared inspection')
    and reports.id=2

The subject can be closed.

Select with own array. PostgreSQL 9.5

2 Answers2