Zadanie #8469

Refactoring zapytania w a_report_get_annotation_relation_types

Added by Michał Marcińczuk over 5 years ago. Updated over 5 years ago.

Status:NowyStart date:10 Jan 2018
Priority:NiskiDue date:
Assignee:-% Done:

0%

Category:-Estimated time:4.00 hours
Target version:Release TBD

Description

W miarę możliwości należy uprościć zapytanie w a_report_get_annotation_relation_types poprzez zastąpienie zagnieżdżonych zapytań i operatorów IN złączeniami i warunkami.

            $sql = "SELECT rt.id, rt.name, rt.description, rs.name AS set_name  FROM relation_types rt " .
                  " JOIN relation_sets rs USING (relation_set_id)" .
                  " JOIN corpora_relations cr ON cr.relation_set_id = rs.relation_set_id AND cr.corpus_id = ? " .
                  " WHERE (rt.id IN (" .
                         "SELECT relation_type_id " .
                         "FROM relations_groups " .
                         "WHERE part='source' " .
                              "AND (" .
                              "annotation_set_id=(" .
                                   "SELECT group_id " .
                                   "FROM annotation_types " .
                                   "WHERE annotation_type_id=(" .
                                         "SELECT type_id " .
                                         "FROM reports_annotations " .
                                         "WHERE id=?" .
                                   ")" .
                              ") " .
                              "OR " .
                              "annotation_subset_id=(" .
                                   "SELECT annotation_subset_id " .
                                   "FROM annotation_types " .
                                   "WHERE annotation_type_id=(" .
                                        "SELECT type_id " .
                                        "FROM reports_annotations " .
                                        "WHERE id=?" .
                                   ")" .
                              ") " .
                         ") " .
                ") AND rs.relation_set_id IN(".implode(",", array_fill(0, count($relationSetIds), "?")).") )
                ORDER BY rs.name, name";

History

#1 Updated by Michał Marcińczuk over 5 years ago

  • Estimated time set to 4.00

Also available in: Atom PDF