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