[FOLIO-1473] Expose full CQL in the Inventory app search box Created: 06/Sep/18  Updated: 25/Jun/20  Resolved: 23/Jun/20

Status: Closed
Project: FOLIO
Components: None
Affects versions: None
Fix versions: None

Type: New Feature Priority: P3
Reporter: Charlotte Whitt Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: for-next-sprint, inventory, search_enhancements
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original estimate: Not Specified

Issue links:
Blocks
blocks MODCXMUX-27 Quotes are not preserved for explicit... Closed
blocks UIIN-305 Boolean Search for Inventory (1st ite... Closed
Cloners
clones FOLIO-445 expose full CQL in the User app searc... Closed
Relates
relates to CQLPG-24 CQL translator: support Array=String Closed
relates to UXPROD-1015 Boolean/Query Search for Users Draft
Sprint:
Development Team: Core: Platform

 Description   

Copy of description in FOLIO-445 Closed (for the User app):
Currently we simply allow putting in a plain term that consequently gets quoted and a query for a specified index(es) gets generated.

(Guys, before we go ahead with that I would like to ensure we have a consensus on this. I think this is the right thing to do since it gives full control to our power users. Lets plan a meeting if you feel we ought to discuss this)



 Comments   
Comment by Mike Taylor [ 06/Sep/18 ]

To make this happen, we need to change the way we build queries and submit them to the back-end so that we have the front-end module configure how the CQL-to-PostgreSQL translator interprets queries against no specific index (or, equivalently, against cql.serverChoice.

IIRC, the cql2pg translator already has that facility – is that right, Julian Ladisch?

If so, then we will need to have each of the back-end modules wire out an endpoint that can be used to call that configuration API, so that when we enter (for example) the Inventory module, we'll call something like

PUT /inventory-storage/cql-config/defaultIndexes

title
contributor
isbn
issn

and subsequent queries will be expanded accordingly on the server side:
water -> (title="water" or contributor="water" or isbn="water" or issn="water")
or, more interestingly:
smith or jones -> (title="smith or jones" or contributor="smith or jones" or isbn="smith or jones" or issn="smith or jones")

(This may require enhancement of the Stripes API to give modules a way to send such configuration when we switch to them; more likely, one of the existing React lifecycle methods will give us what we need.)

Those who have been paying careful attention will notice that in the list of indexes to search by default, I used three (contributor, isbn, issn) that do not yet exist. They will need to be added and supported, in place of the present very nasty hacks whereby a search for water results in this monstrous query being sent:

(title="water*" or contributors adj "\"name\": \"water*\"" or identifiers adj "\"value\": \"water*\"")

The present query "works", but only by knowing the detailed JSON structure of the records in the PostgreSQL database – the very thing that we have repeatedly told people on the Slack channels not to rely on. So as a sort of Step Zero, we'll need to get rid of this hack and replace it with support for good, well-behaved CQL queries.

Comment by Julian Ladisch [ 06/Sep/18 ]

There is a code example for setting server choice indexes on https://github.com/folio-org/cql2pgjson-java#usage

Comment by Julian Ladisch [ 06/Sep/18 ]

CQL does not have support for arrays. See https://folio-org.atlassian.net/browse/CQLPG-24 "CQL translator: support Array=String".

Comment by Mike Taylor [ 06/Sep/18 ]

I do not understand the relevance of that observation.

Comment by Julian Ladisch [ 06/Sep/18 ]

String fields can easily queried using CQL syntax: "title=smith or title=jones"
A field like identifiers is an array element. It cannot be queried in an easy way like a string field. The schema does not contain ISBN at all: https://s3.amazonaws.com/foliodocs/api/mod-inventory-storage/instance-storage.html#instance_storage_instances__instanceId__get
There is substantial work needed to come up with a solution that works for all those cases.

Comment by Mike Taylor [ 06/Sep/18 ]

Julian Ladisch Yes, there is work involved! But that isn't a reason not to do it.

We need, from the client, to use CQL to express in precise yet abstract terms what we want to find – e.g. issn= 0567-7920. It's then up to the back end (whether mod-inventory-storage of cql2pgjson) to translate that into whatever the low-level equivalent is.

But for application-level client code to know the internal JSON Schema – that's really unacceptable. It should never have been committed, let alone survived this long!

Comment by Julian Ladisch [ 06/Sep/18 ]

Yes, I fully agree to and support your last post!

Comment by Cate Boerema (Inactive) [ 22/Jun/20 ]

Charlotte Whitt can we close this as done?

Comment by Charlotte Whitt [ 23/Jun/20 ]

Yes, that's solved when we implemented Query search in Instance, Holdings, and Item segment.

Comment by Zak Burke [ 24/Jun/20 ]

Short version: I'm not sure this was solved when we implemented query search. Yes, full CQL is exposed, but not in a realistically useful way for array fields.

Long version: Julian Ladisch commented that, "A field like identifiers is an array element. It cannot be queried in an easy way like a string field" and Mike Taylor added, "We need, from the client, to use CQL to express in precise yet abstract terms what we want to find – e.g. issn= 0567-7920. It's then up to the back end (whether mod-inventory-storage of cql2pgjson) to translate that into whatever the low-level equivalent is."

We don't really do that. We handle array searches like ISBN with a special case in the search-options menu. Choosing "Identifier (all)" and entering a value generate a CQL query like

query = (identifiers =/@value "9786316800312") sortby title

Choosing "Identifier - ISBN" generates a CQL query like

query = (identifiers =/@value/@identifierTypeId="8261054f-be78-422d-bd51-4ed9f33c3422" "9786316800312") sortby title

That's a long way from

query = (isbn="9786316800312")

There is not an easy way an end user could use "Query search" to search for a specific "System control number", for example.

Related, the shape of the instance record includes:

    "identifiers" : [ {
      "identifierTypeId" : "5d164f4b-0b15-4e42-ae75-cfcf85318ad9",
      "value" : "ocn968777846"
    }, {
      "identifierTypeId" : "8261054f-be78-422d-bd51-4ed9f33c3422",
      "value" : "9786316800312"
    },
    ...
    ]

It seems unfortunate that the identifier-type-name is not included, i.e. to convey that the ID-type 8261054f... corresponds to ISBN.

Comment by Mike Taylor [ 24/Jun/20 ]

Absolutely right. (And this comes back to the decision some time ago to make the UUID "8261054f-be78-422d-bd51-4ed9f33c3422", rather than the name "isbn", the canonical way to specify the ISBN identifier-type in FOLIO.)

The key observation here is that, conceptually, CQL supports a set of searchable indexes that are not tied to the physical fields of the database being search in. In practice it's often useful to provide a one-to-one mapping from database fields to searchable indexes (e.g. search in the index title to do a search in the title field) — but it is absolutely the intention of CQL that indexes should be defined that do not have any direct equivalent field in the database. In the present case, cql2pgjson should translate the CQL query isbn=9786316800312 into whatever bit of SQL does the appropriate thing with the special UUID.

Comment by Julian Ladisch [ 24/Jun/20 ]

MODINVSTOR-413 Closed ( https://github.com/folio-org/mod-inventory-storage/commit/46bbea34b4a68ea861c34e1be96fa3e6e68b1db1 ) added the isbn keyword to CQL allowing to enter

isbn=9780262012102

into the "Query search" box.
Example: https://folio-snapshot-stable.aws.indexdata.com/inventory/view?qindex=querySearch&query=isbn%3D9780262012102&sort=title

Comment by Mike Taylor [ 24/Jun/20 ]

That's brilliant, Julian! You are several dozen steps ahead of me

Just for my curiosity: how much of this work was in cql2pgjson and how much in mod-inventory-storage?

Comment by Julian Ladisch [ 24/Jun/20 ]

RMB provides the option to declare an "sqlExpression" in schema.json.
mod-inventory-storage fills it. In this case it not only extracts the isbn it also applies some isbn normalization. See pull request posted above.

Comment by Mike Taylor [ 25/Jun/20 ]

Julian Ladisch That is really elegant!

Generated at Thu Feb 08 23:13:35 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.