Selector for finding pages that have children (or relations)?

[jordanlev]

I have a template for 'order', and another for 'order-item'... each order is like an invoice and each order-item is like a line item on the invoice (for a specific product). What I'd like to do is retrieve all orders that contain a certain product, and I'm not sure how to achieve this in an efficient manner.

The naive approach would be to do something like this:

$find_this_product = $pages->get('template=product, name=the-product-I-want');
$order_items = $pages->find("template=order-item, order_item_product=$find_this_product");
$orders = array();
foreach ($order_items as $order_item) {
  $orders[] = $order_item->belongs_to_order;
}

Problem with that (I'm guessing) is it's going to make a separate database query for each order_item.

To reduce the number of queries, I'm thinking I could instead loop through the "order_items" and combine their order id's into a pipe-delimited string, then use that string of ids as a selector (e.g. $pages->find(id=1|2|3|4|5))... but that seems kind of yucky to me (especially since there might be hundreds or thousands of orders for a particular product). Ideally what I'd like to do is recreate this kind of SQL logic in my selector:

SELECT DISTINCT orders.* FROM orders INNER JOIN order_items ON orders.id = order_items.order_id WHERE order_items.product_id = X;

Is this possible to do in an elegant fashion with the processwire query engine? Or do I need to resort to SQL to grab all the ids and combine them into a string?

Thanks.

EDIT: I realize my title is a bit confusing. When I say "children", I mean "conceptual children"... the orders and order_items (and products) are related to each other by a Page field... I am not necessarily connecting them by parent/child location in the sitemap.

[LostKobrakai]

I've done something like this a few days ago. I use a pagetable in the order template which lists all the order-items. The order-items do have a pagefield for the product and a field for the quantity. The selector to find the order with a given product would be:

template=order, pagetablename.product=$product

This finds all orders which do have a order-item in the pagetable, which holds the given product.