Uploaded image for project: 'RAML Module Builder'
  1. RAML Module Builder
  2. RMB-395

support nested sub queries across multiple tables

    XMLWordPrintable

Details

    • CP: sprint 69
    • 5
    • Core: Platform

    Description

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

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                adam Adam Dickmeiss
                jakub Jakub Skoczen
                Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases