Using expandable rows - show orders table with expanded rows that show the products ordered

App Building
1

I've found this How to use run a query on an expanded row - #8 by Tess but seems it has workarounds to use non-table data in the expanded rows.. but I'm looking for what I think is a normal/simple use case I can't seem to nail down.

Basic ask is:
Table populated w/ a query on the orders table that returns the order data, from a query like select order_id, order_date from orders etc

Enable Expandable Rows for this table, and on each order row, click to expand and see the products and quantity ordered - which comes from DB like select product_id, quantity from order_detail where order_id = 1234

I really need the expanded rows functionality in the use case, versus a solution that would show the order details in another component on the side of the order list, for example.

I tried a few options, and what I really want is to be able to have a query fire where my where clause can be something like:
select * from order_detail where order_id = {{ ordersTable.currentRow.data.order_id }}

1 Like
2

Hi there @mike_394819 and welcome to the forum!

The approach I would take is to join both queries in one, so that for each order row, you already fetch an array of order details within it. This will enable you to use {{ currentRow }} within the components of the expandable row, without having to trigger a query each time a row is expanded.

An example would be:

SELECT 
    o.id,
    o.order_id, 
    o.order_date,
    jsonb_agg(jsonb_build_object('product_id', od.product_id, 'quantity', od.quantity)) AS order_details
FROM 
    orders o
LEFT JOIN 
    order_detail od ON o.order_id = od.order_id
GROUP BY 
    o.id,
    o.order_id, 
    o.order_date;

Remember to go to your "Transform Results" setting in the query and write return formatDataAsArray (data)

You will get a result like this:

[
    {
        "order_id": 1,
        "order_date": "2024-11-01",
        "order_details": [
            {"product_id": 101, "quantity": 2},
            {"product_id": 102, "quantity": 1}
        ]
    },
    {
        "order_id": 2,
        "order_date": "2024-11-02",
        "order_details": [
            {"product_id": 103, "quantity": 5}
        ]
    },
    {
        "order_id": 3,
        "order_date": "2024-11-03",
        "order_details": [
            {"product_id": 104, "quantity": 3},
            {"product_id": 105, "quantity": 4},
            {"product_id": 106, "quantity": 1}
        ]
    }
]

As you can see, order_details now contains information about all of the line items for the order.

So you can add another table or a listview within the expanded row and use {{ {{currentRow.order_details}} as data source

Hope this helps!

1 Like