OrderLine

Type	Table
OrderLine	gdata_order_line

Order item details refer to the specifics of each item within an order, including the selected modifiers and the pricing associated. For instance, if a customer orders a 'Burger' with the modifier 'extra cheese', the order details will include this item, its modifier, and the total price (including any extra charges for the modifier).

Column Name	Data Type	Description
id	varchar(255) NOT NULL	Unique identifier for the order line item.
order_id	varchar(255)	Foreign key reference to the main order table.
orderdisplayid	varchar(255)	Display ID associated with the order for reference.
createdat	timestamp	Timestamp indicating when the order line was created.
createdby	varchar(255)	Identifier for the individual or system that created the order line.
modifiedat	timestamp	Timestamp indicating when the order line was last modified.
modifiedby	varchar(255)	Identifier for the individual or system that last modified the order line.
status	varchar(255)	Current status of the order line.
tags	varchar(255)	Additional labels or tags associated with the order line.
itemname	varchar(255)	Name of the item.
itemtype		varchar(255)
quantity	float8	Quantity of the item ordered.
currencycode	varchar(255)	The currency code used for this line item.
unitprice	float8	Price per unit of the item.
discountamount	float8	The discount amount applied to the line item.
discountcode	varchar(255)	Discount code, if any, applied to the line item.
taxamount	float8	Tax amount applied to the line item.
totalprice	float8	Total price of the line item, including taxes, discounts, and other charges.
itemexternalid	varchar(255)	External ID associated with the item.
iteminternalid	varchar(255)	Internal ID associated with the item.
menuid	varchar(255)	Identifier for the menu associated with the line item.
menuitemid	varchar(255)	ID for the specific menu item.
modifierid	varchar(255)	Identifier for any modifiers applied to the item (like extra cheese or toppings).
recipeid	varchar(255)	Identifier for the recipe associated with the item.
sortingindex	int4	An index number to determine the order in which items appear (e.g., in a receipt or display).
uniquemenuitemid	varchar(255)	A unique identifier for the menu item to identify menu item and it’s modifiers(Each menu item and it’s modifiers has the same unique menu item id)

Get Menu Details Sample Query

WITH
    order_cte AS (  
        SELECT id           AS order_id,
               brandname as brand,
               locationname as location
        FROM   gdata_order
        WHERE  id =  :order_id
    ),

    order_line_cte AS (   
        SELECT order_id,
               itemname     AS menu_item_name,
               menuitemid,
               itemexternalid AS external_id,
               quantity,
               currencycode AS currency_code,
               taxamount AS tax_amount,
               totalprice AS total_price
        FROM   gdata_order_line
        WHERE  order_id in	(SELECT order_id FROM order_cte)
          AND  itemtype = 'MENU_ITEM'
    ),

    menu_item_cte AS (  
        SELECT menuitemid,
               categoryid
        FROM   gdata_menu_item
    ),

    category_cte AS ( 
        SELECT categoryid,
               "name"       AS category_name
        FROM   gdata_menu_category
    )

SELECT
    o.order_id,
    o.brand,
    o.location,
    l.menu_item_name,
    c.category_name,
    l.external_id,
    l.quantity,
    l.currency_code,
    l.tax_amount,
    l.total_price
FROM   order_cte      o
JOIN   order_line_cte l  ON l.order_id   = o.order_id
JOIN   menu_item_cte  m  ON m.menuitemid = l.menuitemid
JOIN   category_cte   c  ON c.categoryid = m.categoryid
ORDER  BY o.order_id, l.menu_item_name;

Updated 15 days ago