[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: |
|
||||||||||||||||||||||||||||||||
| Sprint: | |||||||||||||||||||||||||||||||||
| Development Team: | Core: Platform | ||||||||||||||||||||||||||||||||
| Description |
|
Copy of description in
(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: (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" |
| 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 ] |
|
isbn=9780262012102 into the "Query search" box. |
| 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. |
| Comment by Mike Taylor [ 25/Jun/20 ] |
|
Julian Ladisch That is really elegant! |