Uploaded image for project: 'mod-inventory-storage'
  1. mod-inventory-storage
  2. MODINVSTOR-256

Improve performance of /instance-storage/instances?query=holdingsRecords.permanentLocationId=abc*

    XMLWordPrintable

    Details

    • Template:
    • Sprint:
      Core: Platform - Sprint 56, Core: Platform - Sprint 57, CP: ready for planning, CP: sprint 70, CP: sprint 69, CP: sprint 71, CP: sprint 72, CP: sprint 73, CP: sprint 74, CP: sprint 75, CP: sprint 76, CP: sprint 77, CP: sprint 78
    • Story Points:
      8
    • Development Team:
      Core: Platform

      Description

      UI inventory uses CQL queries like the below, a view is required:

      ((title="*" or contributors adj "\"name\": \"*\"" or identifiers adj "\"value\": \"*\"")) and holdingsRecords.permanentLocationId="fcd64ce1-6995-48f0-840e-89ffa2288371" sortby title
      

      Smallest testcase triggering the view:

      holdingsRecords.permanentLocationId="fcd64ce1-6995-48f0-840e-89ffa2288371"
      

      Resulting SQL:

      lower(f_unaccent(instance_holding_view.ho_jsonb->>'permanentLocationId')) ~
      lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))fcd64ce1-6995-48f0-840e-89ffa2288371($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))'))
      

      Expected:

      The generated Query should not use a view but a nested subquery that performs filtering by permanentLocationId, similar to:

      _id IN (SELECT (jsonb->>'instanceId')::UUID from holdings_record WHERE jsonb->>'permanentLocationId' = 'fcd64ce1-6995-48f0-840e-89ffa2288371')
      

      Acceptance criteria:

      • CQL2PG and RMB that support generation of nested subqueries when a foreign key filtering is invoked in CQL
      • mod-inventory-storage version that supports filtering by location combined with searching for titles, contributors and identifiers and does not use a view but a SQL subquery

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                adam Adam Dickmeiss
                Reporter:
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases