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

filtering /instance-storage/instances by 'query=id=xxx' does not perform

    XMLWordPrintable

    Details

    • Template:
    • Development Team:
      Core: Platform

      Description

      Using CQL to filter the instances by ID results in poor performance.

      Eric Valuk can you please provide a SQL generated in this case

      CQL query:

      /instance-storage/instances?query=id=6312d172-f0cf-40f6-b27d-9fa8feaf332f
      

      Resulting SQL:

      WHERE lower(f_unaccent(instance.jsonb->>'id'))
         ~ lower(f_unaccent('(^|[[:punct:]]|[[:space:]]|(?=[[:punct:]]|[[:space:]]))6312d172-f0cf-40f6-b27d-9fa8feaf332f($|[[:punct:]]|[[:space:]]|(?<=[[:punct:]]|[[:space:]]))')) 
        ORDER BY lower(f_unaccent(instance.jsonb->>'title')) LIMIT 10 OFFSET 0
      

      As UI inventory uses CQL queries like the below, the view cannot be simply removed in order to benefit from CQLPG-58 and instead an alternative is needed. RMB-305 might provide part of that, or this work might need something similar to the proposal in MODINVSTOR-185

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

      Adam Dickmeiss Heikki Levanto Marc Johnson Hongwei Ji Eric Valuk

        TestRail: Results

          Attachments

            Issue Links

              Activity

                People

                Assignee:
                nassar Nassib Nassar
                Reporter:
                jakub Jakub Skoczen
                Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                  Dates

                  Created:
                  Updated:
                  Resolved:

                    TestRail: Runs

                      TestRail: Cases