SPIKE: design foreign key search support

Description

Currently FK searches are performed through a view that is configured in the schema.json. This view is not accounted for by CQLPG so it requires a hard-coded handling in the appropriate APIs (e.g in mod-inventory-storage it is used for instance searching when holding fields, like location or material type, are used for filtering). This is problematic because any SQL optimisations need to be implemented both the in the CQLPG and in the hard-coded code in mod-inventory-storage.

Also, currently the handling for instance_holding view in mod-inventory-storage is broken and generates queries that ignore the fulltext index for titles, see FOLIO-1920 for details.

Goal

Propose an approach where information about foreign key and their relations to the table being searched comes from schema.json and drives the SQL generation in CQLPG directly. This can have two different forms:

• use the existing schema.json information that is responsible for setting up views and generate queries that utilize those views directly in CQLPG. This might require more complex traverse of the table and view structure by CQLPG, based on what fields are included in the query (e.g when the query includes fields that are not contained within the base table specified when invoking CQLPG, CQLPG would have to find the required view and use that instead)

• redesign how the FK and relationship information is stored in schema.json and based on the information generate nested SQL queries directly in CQLPG not using views. This on the other hand will result in a more complex SQL query (nested)

Notes

Note 1: besides standard standard joins we also have a requirement for handling more complex relationships like MODINVSTOR-277 where we need additional logic to generate an overidden (effective) location value. This most likely not going to he handled in this issues but will require it's own dedicated handling. It should, however, to taken into account when designing the approach.