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.

Aggregate Functions in GET APIs

Aggregate functions allow you to obtain summarized data directly from the API, reducing the need for additional processing. The datetz transform ensures accurate date-based analysis, especially in scenarios involving multiple timezones.

AGGREGATE FUNCTIONS FOR POWERFUL DATA SUMMARIZIATION

Our API now supports a range of aggregate functions, allowing you to perform calculations directly within the API responses. This eliminates the need for external processing, resulting in faster and more efficient data analysis. The following aggregate functions are now available:

SUM: Calculate the sum of values for each specified group.
AVG: Determine the average value within each group.
MIN: Find the minimum value in each group.
MAX: Identify the maximum value, providing insights into the highest values in your data.
COUNT: Efficiently count the number of rows in each group, allowing for quick assessments of data distribution.

To utilize these functions, simply include them in the values_list query parameter of your GET requests to entity APIs.

For example:

?values_list=count(id),sum(value)

datetz TRANSFORM - TIMEZONE-AWARE DATE TRUNCATION

The datetz transform, allows you to truncate timestamps to dates while maintaining the critical timezone offset. This is especially useful when dealing with time-sensitive data and ensures accurate analysis across different timezones. You can apply the datetz transform directly on model filters or within the values_list of an entity GET API request.

For example:

?values_list=create_ts__datetz

Note: While datetz is powerful for data retrieval, it cannot be used as a filter. Ensure that you use regular timestamps for filtering and manually adjust them to the desired date with time set to 00:00:00 in the filter request.

EXAMPLE USAGE

REQUEST:

CopyGET /inventory/?values_list=expiry_date,count(id),sum(curr_qty)&ordering=expiry_date

Result: This request efficiently retrieves the count of IDs and the sum of curr_qty for each expiry_date , grouping and ordering the results by the expiration date.

REQUEST:

GET /inventory/?values_list=create_ts__datetz,count(id),sum(curr_qty)&ordering=create_ts__datetz

Result: Retrieve the count of IDs and sum of curr_qty for each day, grouped by the creation date and ordered while considering the timezone offset.

When using these features, remember the following:

Always include at least one additional field for grouping when using aggregate functions.
Specify the grouping field(s) in the ordering parameter to maintain the desired result order.