Implement Parsing and sql construction changes to bring about joining tables without using views. More information can be found at the related issue
Implement changes in detection to determine presence of search terms / keys that are not present in the current table.
Implement detection of common keys to join both tables on.
Implement sub query using condition sent in to the key determined above with changes needed to correlate it to the common key also determined above.
Determine if In clause is needed and include it in the translation.(see below)
We are not going to resolve the duplicate result (potential) issue (aka DISTINCT) within this ticket.
Requirement: the FK information must be in the schema.json
How to determine location of the field:
1. Each table has a list of indices (index, uniqueIndex, ginIndex, fulltextIndex) if a field matches – we generate query local to the original table.
2. In case the field doesn't match AND the index name contains a dot (like "holdingsRecord.permanentLocationId") we take the string before the dot and convert it to a table name (we need to lowercase and remove underscored) and we check if the string after the dot matches a field name in that table AND that the two tables are linked via a foreign key (either parent->child or child->parent).
mod-orders-storage query – find all purchase order records that already have a least one po_line record (parent -> child example):
we will support notation in cql like
id = poLine.purchaseOrderId
This would result in sql added to the where clause(note the mod-order-storage example below):
jsonb->>'id' in (Select jsonb->>'purchaseOrderId' from diku_mod_orders_storage.po_line )
mod-inventory-storage query - find all instance records that have a least one holdings record that has a specific permanent location (parent -> child example)::
will need to translate into
select jsonb->> 'permanentLocationId'
from diku_mod_inventory_storage.holdings_record where (lower(f_unaccent(instance.jsonb ->> 'id'::text)) = lower(f_unaccent(holdings_record.jsonb ->> 'instanceId'::text)))) LIKE lower(f_unaccent('53cf956f-c1df-410b-8bea-27f712cca7c0')