I can create a view of all Woocommerce orders and show also all products ordered in each order by using "wpv-ordered-product-ids" in the loop.
As I would like to show only orders containing certain products, is it possible to use the same field 'wpv-ordered-product-ids" also in the filter section?
As I would like to show only orders containing certain products, is it possible to use the same field 'wpv-ordered-product-ids" also in the filter section?
Not really, no. The wpv-ordered-product-ids shortcode does not represent a single custom field value, but the results of custom SQL queries on the wp_woocommerce_order_items and wp_woocommerce_order_itemmeta tables. A View cannot query proprietary data tables like these (WP_Query cannot query these tables, and Views implement WP_Query). You would need to add custom SQL to query these DB tables directly, or utilize the WooCommerce API to get those Order IDs some other way. I do not know if there is a WooCommerce API available to query Orders directly by Product ID. But let's assume there is a way, and you know how to get an array of Order IDs by Product ID in PHP somehow. You could then use the Views Filter wpv_filter_query to manipulate an Order View's $query_args and set the post__in argument value to be that array of Order IDs, such that the query only returns those Orders in the View results.
Example showing how to perform a custom lookup (in this case, querying related posts using the post relationships API) and manipulate post__in with the results of that query using a wpv_filter_query filter:
https://toolset.com/forums/topic/wpv_filter_query-filter-by-post-relationship/#post-1197193
Maybe you have another idea for this case.
It's for a site selling event tickets, where customers can add dinner menus as perks to their order.
Both tickets and menus are WooCommerce products, but while the events are different, the dinner menus always keep the same product's id.
That means we could have this scenario:
PRODUCTS
Basic Menu = product_id 1
Deluxe Menu = product_id 2
Event A = product_id 123
Event B = product_id 124
Event C = product_id 125
ORDERS
Order 1 → 2xEvent A (seat 11-12) + 2xMenu Basic
Order 2 → 2xEvent A (seat 13-14) + 1xMenu Deluxe
Order 3 → 3xEvent B (seat 11-12-13) + 1xMenu Basic + 1xMenu Deluxe
Order 4 → 1xEvent C (seat 11) + 1xMenu Deluxe
For the kitchen staff we need to compile a report like this:
EVENT A
2 Basic Menu (11-12)
1 Deluxe Menu (13)
EVENT B
1 Basic Menu (11)
1 Deluxe Menu (12)
EVENT C
1 Deluxe Menu (11)
Any tip/hint is really appreciated.
Thanks.
I think you're going to need a considerable amount of custom code here, regardless of how you implement this. I could try to give more guidance but I need more information.
For the kitchen staff we need to compile a report like this:
- Will the report always include all Events, or just a few events? If just a few Events, how do you plan to determine which Events are in the report - predefined in wp-admin, or User-defined like a custom search View? What about sorting those Events, is there an important sorting criteria? Front-end or backend sorting?
Order 2 → 2xEvent A (seat 13-14) + 1xMenu Deluxe
---
EVENT A...1 Deluxe Menu (13)
- I see Order 2 has two Event A seats and 1 menu. Which seat receives the menu? In other words, why is it 1 Deluxe Menu (13) and not 1 Deluxe Menu (14) - how is the relationship between menu item and seat determined?
2 Basic Menu (11-12)
1 Deluxe Menu (13)
What do the two rows represent here - Orders, or Products? In other words, what happens if there are more Orders for these same menu Products - is the report grouped by Product like this:
EVENT A
4 Basic Menu (11-12, 17, 21)
5 Deluxe Menu (13, 18-20, 25)
...or is it grouped by Order like this:
EVENT A
2 Basic Menu (11-12)
1 Deluxe Menu (13)
1 Basic Menu (17)
3 Deluxe menu (18-20)
1 Basic Menu (21)
1 Deluxe Menu (25)
First of all, I would like to thank you for your help.
- The event id will be stored on a Woocommerce order's custom field, and the report should include only all orders queried for a single event.
Sorting is not important
- In some cases, customers buy 2 Event tickets but only 1 menu, because maybe one of them will only buy beverages on site.
Which seat number will get the menu is not relevant, as both persons will be seated on the same table
- Menus are stored as products, and should be grouped as
EVENT A
4 Basic Menu (11-12, 17, 21)
5 Deluxe Menu (13, 18-20, 25)
Thanks
Okay I see. I don't think Views is the best solution to produce the report you need. Nothing in Views will help you calculate the total number of each Product for a single Event. Any solution here will require custom code to calculate cumulative values, which seems to be a critical feature here. Another critical feature is querying Products by Order, which Views does not support. I assume the seat numbers are stored as meta values on the order item, and that's not available to Views either. So once you've got the custom code in place to get all this information, trying to display it in Views seems pretty pointless. It'll be easier to just loop over the data array you create using a PHP loop.
Ok, got your point.
I am building now a multi-stage query in PHP to retrieve the data.
Let's hope someday Toolset will be able to address the relational tables and build the necessary relationships.
Thanks anyway for your support.
