Querying Multiple Entities in one GET Request using “values_list”

Currently lgfapi GET queries can retrieve data related to one entity, whether it fetches objects (all the fields of the entity) or a specific list of fields (using the fields parameter). With this new experimental feature called “values_list”, you can now fetch data from other related entities also without the need to do multiple GET requests for each entity separately.

The benefits that you will get from this feature is that you can very easily query related data across multiple entities which results in better performance as it fetches less data in the most efficient way possible. It is similar to running a database SQL query by joining multiple tables instead of executing multiple separate queries, one per table.

Note: You need to know the relationships between various entities to use this feature effectively. The entity relationships are described via the self-documenting nature of lgfapi as documented elsewhere in this guide. Another resource is the list of entities (categories) that are listed in the web reports gen2 documentation, along with the relationships.

Example 1: Order type code for a specific order

Let's say that you wanted to look up the company type code for a specific Order with id 1, using lgfapi. In order to get the company type code you needed to make the following sequence of requests.

Step 1: Fetch the order_hdr to get the associated Company’s id.

GET .../entity/order_hdr/1
{
    ...
    "company_id": {
        "id": 48,
        "key": "CM_COMP",
        "url": ".../wms/lgfapi/v10/entity/company/48"
    },
    ....
}

Step 2: Now that you have the company id, you can fetch the company entity to get company type

GET .../entity/company/48
{
    ...
    "company_type_id": {
        "id": 1,
        "key": "R",
        "url": ".../entity/company_type/1"
    },
    ....
}

Now with value list feature, you only need one request.

GET .../entity/order_hdr/1?values_list=company_id__company_type_id__code
{
    "company_id__company_type_id__code": "R"

Note: The response field name will match the field names from the "values_list" parameter.

Let's break down the request:

Making a GET Request to the order_hdr entity
This is the order id value that we are filtering on
New feature values_list
Company_id is the foreign key between order hdr and company entities
Company_type_id is the foreign key between company and company_type entities
Code is what you are looking for ie. Order type code

Example 2: Get the order number, ship via id and the ship via code for all orders of type ‘01’

A query to lookup order hdr records with order type code of ‘01’ can already be represented in lgfapi as:

GET .../lgfapi/v10/entity/order_hdr?type_id__code=01

The ability to retrieve a values_list of field data for a filtered set of object and their related objects may now be used:

GET .../lgfapi/v10/entity/order_hdr?type_id__code=01&values_list=order_nbr,ship_via_id,ship_via_id__code

The ability to retrieve a values_list of field data for a filtered set of object(s) and their related objects may now be used:

GET .../lgfapi/v10/entity/order_hdr?type_id__code__in=01,02,03&values_list=order_nbr,ship_via_id,ship_via_id__code

Get All Information for a Load

Example of a query to get all information for a load:

<wms instance url>/wms/lgfapi/v10/entity/ob_stop_dtl?values_list=ob_stop_id__shipto_facility_id__code:facility,
ob_stop_id__shipto_facility_id__name:Name,
ob_stop_id__shipto_facility_id__address_1:Address,
ob_stop_id__shipto_facility_id__zip:Zip,
ob_stop_id__shipto_facility_id__state:State,
ob_stop_id__load_id__load_nbr:load_nbr,
container_id__container_nbr:container_nbr,
container_id__cntr_inv__item_id__code:item_code,
container_id__cntr_inv__to_from_inv__order_dtl_id__order_id__order_nbr:order_nbr,
container_id__cntr_inv__to_from_inv__order_dtl_id__seq_nbr:order_seq_nbr,
container_id__cntr_inv__batch_number_id:batch&ob_stop_id__load_id__load_nbr=OBL0000003712

Paginated Response

When doing a GET request using filters and values_list, the response structure will still be paginated - the same as a normal request of this type without "values_list":

{
    "result_count": 20,
    "page_count": 1,
    "page_nbr": 1,
    "next_page": null,
    "previous_page": null,
    "results": [
        {
            "order_nbr": "ORDER001",
            "ship_via_id": 123,
            "ship_via_id__code": "UPS1D"
        },
        {
            "order_nbr": "ORDER002",
            "ship_via_id": 456,
            "ship_via_id__code": "FEDX2D"
        },
        ...
    ]
}

Example 3: Do the same query as the previous example, but for a specific order using its id.

The same can also be done with the retrieve GET request using a specific object's ID in the URL:

GET .../lgfapi/v10/entity/order_hdr/123?values_list=order_nbr,ship_via_id,ship_via_id__code

Non-Paginated Response

When doing a GET request using the ID in the URL with values_list, the response will not be paginated - the same as a normal request of this type without "values_list".

{
    "order_nbr": "ORDER001",
    "ship_via_id": 123,
    "ship_via_id__code": "UPS1D"
}

Differences in Response Structure

There are several differences between the data returned by a standard GET query (ie. One retrieving objects or fields of one entity) versus one using values_list:

Related objects (foreign keys) are represented in the normal flow as a nested object with the fields: id, url, key. When using values_list, the result will only be the integer "id" value for the related object.

"company_id": {
    "id": 48,
    "key": "CM_COMP",
    "url": ".../wms/lgfapi/v10/entity/company/48"
}
vs
{
    "company_id": 48
}

You will not get the enriched response structure like in the standard GET query. For example, when doing a normal GET for the entity order_hdr, the response structure includes additional fields like "order_dtl_set", "order_lock_set", and "order_instructions_set". These related/child references enrich the content by being present in the representation, but are not actually fields directly defined on the "order_hdr" entity - they are determined at runtime by the serializer. These types of fields will not work with "values_list" and will result in an error.

Creating Aliases

You also have the option to "rename" the fields in the output to reduce the field name complexity and the overall size of the response payload. It is important to have unique aliases. By default, fields names will match the value from the values_list:

GET .../entity/allocation/1?values_list=id,order_dtl_id__order_id__order_nbr
{
    "id": 1,
    "order_dtl_id__order_id__order_nbr": "ORDER123"
}

You may override the default names by giving an alias delimited by a colon character ":" for the given field in the request URL query string:

GET .../entity/allocation/1?values_list=id:foo,order_dtl_id__order_id__order_nbr:order_nbr
{
    "foo": 1,
    "order_nbr": "ORDER123"
}

Assumptions

The output fields name(s) must be unique:
You cannot repeat the same alias in a values_list
You cannot use an alias that is the same name as another field in the values list
An error like, "Values list alias names must be unique.", will be returned and will specify all of the name(s) in violation.

Distinct Parameter

lgfapi will support the new "distinct" query parameter for GET requests when also doing a values_list style GET request. Values list requests are used for making more direct calls to fetch targeted relational table data without the structure of an entity serializer. Due to this, there may be instances where the resultant data is repeated. For example: An OBLPN may have multiple allocation records pointing back to multiple order details for the same order. A values list request for the order_nbr would have as many repeated results as allocation records. This follows the same principles as the underlying DB query.

Assumptions

Only works for GET requests using the values_list query parameter
Only works for list-style (paginated) requests. Will not work when querying by id.
If used in unsupported situations, the parameter will be ignored (no error).

Example Usage - List (Paginated) Response

Example 1 - Fetch order_nbr without using distinct

GET .../entity/order_dtl/?order_id=123&values_list=order_id,order_id__order_nbr:order_nbr

{
    "result_count": 2,
    "page_count": 1,
    "page_nbr": 1,
    "next_page": null,
    "previous_page": null,
    "results": [
        {
            "order_id": 123,
            "order_nbr": "ORDER123"
        },
        {
            "order_id": 123,
            "order_nbr": "ORDER123"
        },
    ]
}

Example 1 - Fetch unique order_nbr using distinct

GET .../entity/order_dtl/?order_id=123&values_list=order_id,order_id__order_nbr:order_nbr&distinct=1

{
    "result_count": 1,
    "page_count": 1,
    "page_nbr": 1,
    "next_page": null,
    "previous_page": null,
    "results": [
        {
            "order_id": 123,
            "order_nbr": "ORDER123"
        }
    ]
}

Example 2 - Get unique container status for each of the first 100 containers selected.

GET .../entity/container/?limit=100&values_list=status_id__description:container_status&distinct=true

{
    "result_count": 4,
    "page_count": 1,
    "page_nbr": 1,
    "next_page": null,
    "previous_page": null,
    "results": [
        {
            "container_status": "Allocated"
        },
        {
            "container_status": "Cancelled"
        },
        {
            "container_status": "Consumed"
        },
        {
            "container_status": "Delivered"
        }
    ]
}

Retrieve (Single-Object) Response

Flow where entity "id" is included as part of the URL.

This is not supported. Since this GET request style will always return a single object representation, there is no meaning to "distinct" as values cannot be repeated. If the query parameter is included in this flow, it will be ignored.