Hello All
Suppose I have this datamodel with a many to many relationship between Order and Product.
ClearCollect(
Order,
{ id: 1, customer: "Tracy" },
{ id: 2, customer: "Peter" },
{ id: 3, customer: "Boris" },
{ id: 4, customer: "Marie" },
{ id: 5, customer: "John" }
);
ClearCollect(
Product,
{ id: 1, name: "Soap" },
{ id: 2, name: "Beer" },
{ id: 3, name: "Olives" },
{ id: 4, name: "Oil" },
{ id: 5, name: "Oat" }
);
ClearCollect(
OrderDetail,
{ order: 1, product: 5, qty: 12 },
{ order: 1, product: 4, qty: 10 },
{ order: 1, product: 3, qty: 22 },
{ order: 2, product: 3, qty: 34 },
{ order: 3, product: 4, qty: 88 },
{ order: 3, product: 5, qty: 11 },
{ order: 3, product: 1, qty: 23 },
{ order: 4, product: 2, qty: 12 },
{ order: 4, product: 1, qty: 65 },
{ order: 5, product: 1, qty: 41 },
{ order: 5, product: 1, qty: 34 }
);
And now I want to fill a gallery with the equivalent of this query:
select o.*
from Order o
join OrderDetail d on (o.id = d.order)
join Product p on (p.id = d.product)
where p.name like ':bind%'
order by p.name
So I want a collection of orders which contain specific products sorted by the names of the products. The same order may (must) appear multiple times in the result. The only way to reach that result is this code which works but which I find verbose and not very beautyful.
ClearCollect(
tmpProduct,
ShowColumns(
SortByColumns(
Filter(
Product,
StartsWith(name, txtProduct.Text)
),
"name"
),
"id"
)
);
ClearCollect(
tmpOrderDetail,
AddColumns(
tmpProduct,
"_product",
Filter(
OrderDetail,
product = id
)
)
);
Clear(tmpOrder);
ForAll(
tmpOrderDetail,
Collect(
tmpOrder,
tmpOrderDetail[@_product]
)
);
Clear(Result);
ForAll(
tmpOrder,
Collect(
Result,
Filter(Order, id = tmpOrder[@order])
)
)
Does anyone have the solution to the general question in a N x M relationship:
How to get a list of N based on filtering and sorting on attributes of M?
Thanks for reading this
