LIMIT a nested table

musinik

Hi!

I have two related tables, like "orders" and "order_items" with relation 1 to N.
DataTables allows to make CHILD rows which is very useful - I can have 1 order in row and expand it and see items in it.
I want to make a serverside selection with limit to number of rows(which should be orders, not order items).

To bring more grasp, the result will look e.g. like this (without details, just to see structure):
Order #, order item number - {1:{1,2,3},2:{1,2},3:{1},4:{1,2}}

So I need to limit by orders, but not order items, which is limited by default when you limit the whole select.

The question is:
Can this somehow be realized in DataTables without rewriting the source code (or with minimum efforts in this way)?
E.g. if I can request in DataTables through a Strored Procedure (where I will deal with my custom limit) ?

musinik

The example SQL would be:

SELECT o.order_number, i.item_number
FROM (SELECT * FROM orders LIMIT a,b) AS o JOIN order_items AS i ON (o.id = i.order_id)

musinik

I coppied the original "simple" SSP method and modified it, and also the data output.
That appeared not complicated. The script is easy readable.

glenderson

I would do this different using row grouping.

https://datatables.net/examples/advanced_init/row_grouping.html

perform a full inner join (or left join if necessary) such that you will have many rows for each order (depends upon the number of order items). Then, using row grouping to gather all the "Order 1" items together, and all the "Order 2" items. The orders will be separated by a "Grouping Row"

The advantage is that you can then also group on the Ordered item. Which can then show the ordered items as the Group with the order numbers below them.

musinik

Thanks for the suggestion!