Details
-
New Feature
-
Status: Closed (View Workflow)
-
P2
-
Resolution: Done
-
None
-
customfield_11100 26449
-
CP: sprint 66, CP: sprint 67
-
3
-
Core: Platform
Description
Implement a search term of the form parentTable.field where parentTable is referenced from the current table (the child table) using a foreign key relation.
Implement changes in detection to determine presence of search terms / keys that are not present in the current child table but where the search term is of the form parentTable.field and parentTable is listed as targetTable in the current child table's foreignKeys section in schema.json.
Implement the clause that uses the parent table field using the foreign key relation.
Note: The current child record can only have a single parent record. See CQLPG-99 for the inverse relation (parent->child query) where the current parent record can have several child records and one matching child record is sufficient.
CQL example 1:
Find purchase order lines where the purchase order is assigned to a given staff person and sort them by receiptDate.
purchase_order.assignedTo=="ab18897b-0e40-4f31-896b-9c9adc979a88" sortBy receiptDate DESC
This makes use of po_lines purchaseOrderId field that is a foreign key to purchase_order.id. po_lines is the child table, purchase_order is the parent table.
CQL example 2:
Find all items that are checked out and have the call number prefix GEO:
status.name=="Checked out" AND holdings_record.callNumberPrefix=="GEO"
This makes use of item's holdingsRecordId field that is a foreign key to holdings_record.id. item is the child table, holdings_record is the parent table.
View examples:
items_mt_view: item.materialTypeId=material_type.id
instance_holding_view: holdings_record.instanceId=instance.id
users_groups_view: users.patronGroup=groups.id
orders_view: po_line.purchaseOrderId=purchase_order.id
order_lines_view: po_line.purchaseOrderId = purchase_order.id
records_view: records.rawRecordId=raw_records.id AND records.parsedRecordId=marc_records.id AND records.errorRecordId=error_records.id
source_records_view: records.rawRecordId=raw_records.id AND records.parsedRecordId=marc_records.id
note_view: note_data.typeId=note_type.id
note_type_view: note_data.typeId=note_type.id
Select examples:
SELECT_PACKAGE_IDS_BY_TAG : SELECT packages.id FROM packages LEFT JOIN tags ON tags.record_id = packages.id WHERE tags.tag IN (%s) and packages.id LIKE ? SELECT_PACKAGES_WITH_TAGS: SELECT packages.id as id, packages.name as name, packages.content_type as content_type, tags.tag as tag FROM packages LEFT JOIN tags ON tags.record_id = packages.id AND tags.record_type = 'package' WHERE packages.id IN (%s) SELECT_PACKAGES_WITH_TAGS_BY_IDS : SELECT packages.id as id, packages.name as name, packages.content_type as content_type, tags.tag as tag FROM packages LEFT JOIN tags ON tags.record_id = packages.id AND tags.record_type = 'package' WHERE packages.id IN (%s) SELECT_TITLES_BY_RESOURCE_TAGS: SELECT DISTINCT (regexp_split_to_array(resources.id, '-'))[3] as id, resources.name as name, holdings.jsonb as holding FROM resources LEFT JOIN tags ON tags.record_id = resources.id AND tags.record_type = 'resource' LEFT JOIN holdings ON holdings.id = resources.id WHERE tags.tag IN (%s) SELECT_RESOURCE_IDS_BY_TAG : SELECT resources.id FROM resources LEFT JOIN tags ON tags.record_id = resources.id WHERE tags.tag IN (%s) and resources.id LIKE ? SELECT_RESOURCES_WITH_TAGS: SELECT resources.id as id, resources.name as name, tags.tag as tag FROM resources LEFT JOIN tags ON tags.record_id = resources.id AND tags.record_type = 'resource' WHERE resources.id IN (%s) SELECT_TAGGED_PROVIDERS: SELECT DISTINCT providers.id as id, providers.name FROM providers LEFT JOIN tags ON tags.record_id = providers.id AND tags.record_type = 'provider' WHERE tags.tag IN (%s)
TestRail: Results
Attachments
Issue Links
- blocks
-
RMB-395 support nested sub queries across multiple tables
-
- Closed
-
-
UXPROD-1818 RMB 26 release features and core module rollout (performance, array search and cross-table search)
-
- Closed
-
- is required by
-
RMB-411 Implement poLine.id = * style cql for subquery
-
- Closed
-
- relates to
-
RMB-385 add 'queryIndexName' to schema.json and allow compound indexes
-
- Closed
-
-
RMB-448 Fix foreign key documentation in CQL2PG
-
- Closed
-
-
RMB-391 Implement foreign key support for CQLPGJson Parser (two tables, parent->child)
-
- Closed
-