We need either an iterative or recursive method for creating subquery joins based on walking the chain in some way from the goal table to the source table.
1. we need to establish a chain of foreing keys to make this. One option is to walk the table defintion following the FK links. Another option is that we encode this information in the schema.json (points at JOIN table explicitly).
mod-inventory-storage query - find the instance of the holding of the item of barcode 706949453641
This is checked in the unit test InstanceStorageTest.canSearchByBarcode(): https://github.com/folio-org/mod-inventory-storage/blob/v15.4.0/src/test/java/org/folio/rest/api/InstanceStorageTest.java#L925
There also is this extended test in InstanceStorageTest.canSearchByBarcodeAndPermanentLocation():
CQL: item.barcode==706949453641 and holdingsRecords.permanentLocationId==123
receiving_history_view: pieces.poLineId=po_line.id AND po_line.purchaseOrderId=purchase_order.id
This is from https://github.com/folio-org/mod-orders-storage/blob/master/src/main/resources/templates/db_scripts/schema.json and is used by the /orders-storage/receiving-history API endpoint.
Example for instance - holdings - items:
Note that "tableAlias" and "targetTableAlias" are optional if the child->parent or parent->child name is not needed.
This is an example that shows how to resolve an ambiguity (same "targetTable") using "tableAlias" and "targetTableAlias":
Running CQL loanType.name == "Can circulate" against the item endpoint returns all items where the item's permanentLoanTypeId points to a loan_type where the loan_type's name equals "Can circulate".
Running CQL temporaryLoanType.name == "Can circulate" against the item endpoint returns all items where the item's temporaryLoanTypeId points to a loan_type where the loan_type's name equals "Can circulate".
Running CQL itemWithPermanentLoanType.status == "In transit" against the loan_type endpoint returns all loan_types where there exists an item that has this loan_type as a permanentLoanType and where the item's status equals "In transit".
Running CQL itemWithTemporaryLoanType.status == "In transit" against the loan_type endpoint returns all loan_types where there exists an item that has this loan_type as a temporaryLoanType and where the item's status equals "In transit".