support nested sub queries across multiple tables
Description
Environment
Potential Workaround
blocks
duplicates
is blocked by
relates to
Checklist
hideTestRail: Results
Activity
Adam Dickmeiss August 7, 2019 at 6:32 PM
Added https://folio-org.atlassian.net/browse/RMB-450#icft=RMB-450 .. I'm sure others will follow
Hongwei Ji August 6, 2019 at 11:23 PM
Update: I created a PR https://github.com/folio-org/raml-module-builder/pull/487 for this ticket. The implemented approach uses foreignKeys info to construct subqueries across multiple tables (one-way, either child -> parent or parent -> child, not mixed). Added optional tableAlias and targetTableAlias attributes to foreignKeys to address potential naming collision.
API call to /*instance?query=item.barcode == 7834324634* will generate nested subquery like below. A quick test in perf env shows the execution time is under 1 ms.
WHERE instance.id IN ( SELECT (holdings_record.jsonb->>'instanceId')::UUID from holdings_record WHERE holdings_record.id IN ( SELECT (item.jsonb->>'holdingsRecordId')::UUID from item WHERE lower(f_unaccent(item.jsonb->>'barcode')) LIKE lower(f_unaccent('7834324634'))))folio=# explain analyze select * from instance WHERE instance.id IN ( SELECT (holdings_record.jsonb->>'instanceId')::UUID from holdings_record WHERE holdings_record.id IN ( SELECT (item.jsonb->>'holdingsRecordId')::UUID from item WHERE lower(f_unaccent(item.jsonb->>'barcode')) LIKE lower(f_unaccent('7834324634'))));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=17.61..20.02 rows=1 width=882) (actual time=0.114..0.115 rows=1 loops=1)
-> HashAggregate (cost=17.18..17.19 rows=1 width=350) (actual time=0.103..0.103 rows=1 loops=1)
Group Key: ((holdings_record.jsonb ->> 'instanceId'::text))::uuid
-> Nested Loop (cost=9.14..17.17 rows=1 width=350) (actual time=0.101..0.102 rows=1 loops=1)
-> HashAggregate (cost=8.71..8.72 rows=1 width=592) (actual time=0.084..0.084 rows=1 loops=1)
Group Key: ((item.jsonb ->> 'holdingsRecordId'::text))::uuid
-> Index Scan using item_barcode_idx on item (cost=0.43..8.71 rows=1 width=592) (actual time=0.081..0.081 rows=1 loops=1)
Index Cond: (lower(public.f_unaccent((jsonb ->> 'barcode'::text))) = '7834324634'::text)
Filter: (lower(public.f_unaccent((jsonb ->> 'barcode'::text))) ~~ '7834324634'::text)
-> Index Scan using holdings_record_pkey on holdings_record (cost=0.44..8.46 rows=1 width=366) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: (id = ((item.jsonb ->> 'holdingsRecordId'::text))::uuid)
-> Index Scan using instance_pkey on instance (cost=0.44..2.83 rows=1 width=882) (actual time=0.008..0.008 rows=1 loops=1)
Index Cond: (id = ((holdings_record.jsonb ->> 'instanceId'::text))::uuid)
Planning time: 3.439 ms
Execution time: 0.229 ms
(15 rows)BTW, I did not have time to do through tests or update mod-inventory-storage yet.
Julian Ladisch August 5, 2019 at 5:13 PM
If we want a better name for "targets" we even might use "fieldNames" to be consistent with the single table foreignKeys "fieldName" property:
{
"tableAlias": "item",
"targetTableAlias": "instance",
"fieldNames": [ "holdingsRecordId", "instanceId"]
}Jakub Skoczen August 1, 2019 at 12:06 PM
@Hongwei Ji @Julian Ladisch I like the final proposal but I would recommend changing the property name from "targets" to "joins" if you don't mind.
Can we please put the final proposal in the description? Thanks!
Julian Ladisch July 29, 2019 at 6:09 PM
@Hongwei Ji Thanks for the suggestion to simplify the "targets" property by removing the "targetTable" property.
The "targets" property
"targets": [
{
"fieldName": "holdingsRecordId",
"targetTable": "holdings_record"
},
{
"fieldName": "instanceId",
"targetTable": "instance"
}
]becomes
"targets": [ "holdingsRecordId", "instanceId"]Then schema.json looks like this:
{
"tableName": "instance",
},
{
"tableName": "holdings_record",
"foreignKeys": [
{
"fieldName": "instanceId",
"tableAlias": "holdingsRecord",
"targetTable": "instance",
"targetTableAlias": "instance",
"tOps": "ADD"
}
]
},
{
"tableName": "item",
"foreignKeys": [
{
"fieldName": "holdingsRecordId",
"tableAlias": "item",
"targetTable": "holdings_record",
"targetTableAlias": "holdingsRecord",
"tOps": "ADD"
},
{
"tableAlias": "item",
"targetTableAlias": "instance",
"targets": [ "holdingsRecordId", "instanceId"]
}
]
}"tableAlias" and "targetTableAlias" are still needed to enable the alias. "tableAlias": "item" enables the the "item" alias in the instance record. Without the "tableAlias" there will be no alias in the instance record; this can be useful to prevent namespace pollution.
Details
Details
Assignee
Reporter
Priority
P3
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).
Examples:
mod-inventory-storage query - find the instance of the holding of the item of barcode 706949453641
CQL: item.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
https://github.com/folio-org/mod-inventory-storage/blob/v15.4.0/src/test/java/org/folio/rest/api/InstanceStorageTest.java#L973
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.
Decision:
Example for instance - holdings - items:
{ "tableName": "instance", }, { "tableName": "holdings_record", "foreignKeys": [ { "fieldName": "instanceId", "tableAlias": "holdingsRecord", "targetTable": "instance", "targetTableAlias": "instance", "tOps": "ADD" } ] }, { "tableName": "item", "foreignKeys": [ { "fieldName": "holdingsRecordId", "tableAlias": "item", "targetTable": "holdings_record", "targetTableAlias": "holdingsRecord", "tOps": "ADD" }, { "tableAlias": "item", "targetTableAlias": "instance", "fieldNames": [ "holdingsRecordId", "instanceId"] } ] }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":
{ "tableName": "item", "foreignKeys": [ { "fieldName": "permanentLoanTypeId", "tableAlias": "itemWithPermanentLoanType", "targetTable": "loan_type", "targetTableAlias": "loanType", "tOps": "ADD" }, { "fieldName": "temporaryLoanTypeId", "tableAlias": "itemWithTemporaryLoanType", "targetTable": "loan_type", "targetTableAlias": "temporaryLoanType", "tOps": "ADD" } ] }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".