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.
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