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

Implement child->parent foreign key support for CQLPGJson Parser

    XMLWordPrintable

Details

    • 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

            Activity

              People

                evaluk Eric Valuk
                julianladisch Julian Ladisch
                Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases