Woocommerce: Custom order tables + new orders UI

[mikejolley]

These are the steps I think we need to cover (in sequence) in order to migrate orders to a custom DB table (#9735).

_Phase 1: Data Abstraction - 2.x.x_

Phase 1 will be to remove any reliance for core and 3rd party plugins on WP functions, namely post and post meta functions. Also (@claudiosmweb) we really need to start consolidating code used in the API/CLI and WP Backend to update orders. Our current codebase is not DRY at all.

• [x] Introduce wc_get_orders() function to replace get_posts().
• [x] Introduce meta setter and getter functions to avoid any kind of get_post_meta usage, where needed.
• [x] Expand CRUD operations for order classes.
• [x] Replace core usage of get_posts, get_post_meta and direct SQL queries where possible (reports cannot be changed however due to the nature of the complex queries currently required).
• [x] API CRUD usage for order endpoints.
• [x] CLI CRUD usage for order endpoints.

_Phase 2: UI Refresh - 3.x.x_

Next we need to redo the UI to again remove the reliance on WP core. Since we'll have a decent CRUD by this point, we can make use of React/Backbone to make something that performs well and gives a better experience for the store owner.

This will have the side affect of breaking any plugin which adds custom meta boxes, so new hooks and ways of adding custom content will be needed to ensure things are still extensible.

@jameskoster will design new screens to show order data. Without the constraints of WP admin + meta boxes we can do pretty much anything we like here.

• [ ] Replace posts list with custom list table.
• [ ] Replace view/edit order screen with custom UI. This will include new panels for downloadable products to get around the performance issues originality reported in #8589

_Phase 3: Data Migration - 3.x.x_

• [ ] Create new tables; woocommerce_orders and woocommerce_ordermeta
• [ ] Separate payment status from shipped status. Just throwing this on the list but needs more consideration ( @maxrice @justinstern ) to make statuses more flexible,
• [ ] Create migration script (dedicated page with progress bar, or background operation) to migrate posts to orders.
• [ ] Switch CRUD operations to new tables.
• [ ] Rewrite reports to use new tables. Also a good opportunity to do a refresh of the entire reports section once data is easier to access.

I will create issues for the above points in the relevant milestones, however, I wanted to get feedback prior and ensure I have not missed anything. @pmgarman @thenbrent @maxrice @claudiosmweb @mattyza @allendav @justinshreve @jameskoster .

[rahul286]

Wow! :-) :+1:

[claudiosanches]

:smiley:

[lukecav]

:smiley:

[mikejolley]

Non-smiley feedback welcome :D

[pmgarman]

What kind of parameters will wc_get_orders take in an arguments array. My thinking is that what would be taken in while using get_posts would be pretty different from what is taken in on custom tables. Unless the initial version of it while using get_posts was pretty limited and the more advanced parameteters would be added later.

For example: status. If @ 3.0 there becomes a payment and a shipping status, will the wc_get_orders arguments taken a status or go straight to payment_status? Or will status just get deprecated pretty quick in order to move it to payment_status? Obviously it could just _stay_ status but isn't not as obvious when looking at the array of parameters that it is the payment or shipping status.

[mikejolley]

We'd need some of the get_posts args, limit, per page etc + status (we can handle bw compat if we change this in the future), customer_id, etc API has 'status' https://woothemes.github.io/woocommerce-rest-api-docs/#view-list-of-orders

[mikejolley]

@pmgarman did you look at these functions at all - you said you were keen the other day?

[pmgarman]

I've not yet, I'm working through some unrelated reporting work today, tomorrow/Monday I'm planning to get into some abstraction. Was out of office Tuesday/Wednesday.

[mikejolley]

:+1: I'll make a branch once I've taken care of 2.5 release

[jkudish]

API CRUD usage for order endpoints.
CLI CRUD usage for order endpoints.

The CLI and API Cruds can probably be one and the same, with the CLI just being a wrapper on top of the API.

I'm keen to help out with API and UI/React parts of this.

[mikejolley]

@jkudish +1

[thenbrent]

Scary (but exciting) stuff.

The 3 phases sounds like a sane way to tackle it.

What kind of parameters will wc_get_orders take in an arguments array.

For reference, Subscriptions has a wcs_get_subscriptions() method which accepts the following args:

 * 'customer_id' The user ID of a customer on the site.
 * 'product_id' The post ID of a WC_Product object
 * 'subscription_status' Any valid subscription status. Can be 'any', 'active', 'cancelled', 'suspended', 'expired', 'pending' or 'trash'. Defaults to 'any'.
 * 'order_id' The post ID of a shop_order post/WC_Order object which was used to create the subscription
 * 'orderby' The field which the subscriptions should be ordered by. Can be 'start_date', 'trial_end_date', 'end_date', 'status' or 'order_id'. Defaults to 'start_date'.
 * 'order' The order of the values returned. Can be 'ASC' or 'DESC'. Defaults to 'DESC'
 * 'subscriptions_per_page' The number of subscriptions to return. Set to -1 for unlimited. Default 10.
 * 'offset' An optional number of subscription to displace or pass over. Default 0.

Those have been sufficient for our needs, which are obviously more limited than the use cases for wc_get_orders().

You'll also notice we opted for explicit subscription param keys for any subscription data instead of accepting the same param keys get_posts() uses (e.g. subscription_status instead of post_status). But we support the same param keys for query related args, like order and offset. We also extend some get_posts() params, like orderby to accept additional values that relate to a subscription (like ordering by trial_end_date).

I can't say whether this is the best approach for wc_get_orders(), though it works for us pretty well.

This will have the side affect of breaking any plugin which adds custom meta boxes

That doesn't necessarily have to be the case. It will be possible (albeit pretty hacky) to maintain backward compatibility even if using React components for displaying new meta boxes. Something we can look at in more detail when the time comes.

[webdados]

Is this only for orders for now, or are you planning on doing the same for products down the road?

I've got a HUGE website where the products (and the WPML translations of each product) are loaded/updated via a excel file with 40+ fields per product and I use wp_update_post and update_post_meta A LOT!

[claudiosanches]

@webdados only orders for now.

[webdados]

@claudiosmweb "for now" :-/

[BuggeringHell]

This is great for stores with large order volumes, it'd be nice to eventually have the same treatment for products as stores with a large, regularly updated catalog (e.g. CSV imports) would really benefit from it.

[shivapoudel]

:+1:

[webdados]

@BuggeringHell yes, I see it coming along the way. Huge amounts of products is what makes WC websites very slow. Moving them to separate tables would speed things up. I just hope import/export features/API are created. (I know there are tools like WP All Import, but they don't play well with WC+WPML, for example)

[mikejolley]

Products suit posts tables. We also don't need to run complex queries across products like we do orders. So my vote is to leave products where they are.

[BuggeringHell]

Possibly not the product/post rows itself, but the transients/cache? MySQL gets swamped with queries called from delete_version_transients() located in _woocommerce/includes/class-wc-cache-helper.php_. Unless I am mistaken, it is called every time a product is updated? With very large product catalogues, imports become impossibly slow.

[mikejolley]

@BuggeringHell read the release post for 2.5. Transients have been optimised.

[BuggeringHell]

Oh! I recently updated and tried an import to the same result, maybe I should clear all WC transients? My wp_options table hasn't budged much from ~1,200,000 rows.

[mikejolley]

Yeah, flush them all (WP normally does after updates). Sessions are also no longer stored in options table.

[webdados]

@mikejolley :+1:

[maxrice]

:+1:, the sooner we can begin the data abstraction the better.

Separate payment status from shipped status

Absolutely agree with this. I think the way Shopify approaches order statuses is pretty rational and would make for good inspiration:

(see Order API docs)

There's an overall order status, then one for financial status and one for fulfillment status.

[toddlahman]

Although wc_get_orders() will be required, it would be a good approach to call an abstract method that allows the WC custom core table to be queried, or third party table to be accessed with a similar structure, but a custom table name, so the same performance gain can be realized by extending WC in third party plugins when a custom table is needed. Anytime the wheel doesn't need to be reinvented in third party plugins, would mean more consistency throughout the ecosystem.

[pmgarman]

Thanks @maxrice for the notes from Shopify, I think all three of those order statuses would be relevant for WooCommerce orders. Adding a financial_status could mean too in a even further in the future update that partial payments could be supported in WooCommerce even.

[toddlahman]

Partial payments would be awesome! It should be possible with gateways such as Stripe.

[amjad]

Why don't we still have "Order Type" as a default feature in WooCommerce?

[thisisbbc]

Really looking forward to see this change. It's been really a pain to work with a staging platform and try to merge everything when new orders were being registered. I wish I wasn't such a noob with php and js to help you guys. :baby: :+1:

[franticpsyx]

Scary (but exciting) stuff.

+1

[pelmered]

This is very exiting stuff! 😀

Products suit posts tables. We also don't need to run complex queries across products like we do orders. So my vote is to leave products where they are.

@mikejolley: Really? Isn't it more the opposite? Products can be filtered and sorted in so many ways that makes sense. The only complex queries for orders I can think of are search queries for the admin views and reporting(but those queries shouldn't need to run often). The reason I see why you would want to put orders in a separate table is to optimise for writes. That is a problem for stores with a lot of orders.

I think that it's only a matter of time before the need to move products into a new table as well comes and I think it would make a lot of sense to do that at the same time as the orders. A lot of the code could be reused and you wouldn't need to do a large breaking change like this again any time soon.

[webdados]

@pelmered I understand your point, and I do agree with it, but keep in mind that at the frontend, a product is a post like any other. This makes it possible to take advantage of all the plugins out there that interact with "posts", like SEO stuff (as an example).

[dima-stefantsov]

"Products" should stay in wp_posts, what makes WooCommerce slow on 50k+ products is huge wp_postmeta. WC creates ~29 meta rows per every post/product. Everything gets slow when wp_postmeta have few millions rows.

All that needs to be done is flatten this postmeta EAV hell somehow. Move it to new table wp_woocommerce_product_meta, where it takes 1 row per product, not ~29 in common table.

There is kind of solution that does exactly this https://wordpress.org/plugins/horizontal-meta/ , but it's abandoned and "doesn't work well past wp 4.1".

[pmgarman]

As someone who has had over 100M postmeta rows, I disagree with your statement. It's a large database yes, and you just need to have a hosting setup suitable for your site. Products run just fine as post types but with large data sets you do need to be more careful with the plugins and other site setups than just installing whatever plugin you find.

Though at this point this issue is around orders, not products. To keep things on topic if you still disagree perhaps it is best to create. New issue for products and include data and reasoning around why you disagree not just "it's slow" as that's not going to get things anywhere quickly.

[dima-stefantsov]

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1 AND ( ( wp_postmeta.meta_key = '_visibility' AND wp_postmeta.meta_value IN ('visible','catalog') ) ) AND wp_posts.post_type = 'product' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') GROUP BY wp_posts.ID ORDER BY wp_posts.menu_order ASC, wp_posts.post_title ASC LIMIT 48, 12;

Above is the code WP/WC generates for page 5 root of shop products. It joins with postmeta and checks if each product should be counted/displayed or hidden. It takes about 0.8 seconds on ~93k products, on 4.2GHz top i7 PC. Not much faster CPU can get. (ssd, innodb, db warm in cache)

http://4.stefantsov.com/s/?2016.09.13_16.35.14_a0dwycvp.png

You say I am doing it wrong, and there is a way to make browsing faster? Currently page loads more than a second, on too fast pc, taking 100% CPU of one core for the time. You say it can become 10-100 times faster?

I'm not DBA, but I'm seasoned developer, in many languages. General phrases like _"you do need to be more careful with the plugins and other site setups than just installing whatever plugin you find"_ are not about me. Naked WP. WC. 100k products. Opening full products list. You say it can be fast? How? What about 3 000 000 products?

Even more, what about adding products? Adding 50-100k products is not DB-capped on my 4GHz cpu, it's about 300 products/minute with default setup. After that, things become exponentially slower. If WP would use horizontal table for product meta, it would work with millions products no problem. Can I make it work this way now? How does _"hosting setup suitable for my site"_ looks in my case?

My current understanding is that it's impossible with EAV ~29 postmetas per product.
Please open my eyes, tell me I'm wrong. Your 100M postmeta sure sounds impressive.

[webdados]

@dima-stefantsov Just open a new thread for "products" as @pmgarman told you to.

[mikejolley]

All that needs to be done is flatten this postmeta EAV hell somehow. Move it to new table wp_woocommerce_product_meta, where it takes 1 row per product, not ~29 in common table.

If you want to experiment and send a PR I'd love to see that. The CRUD classes in 2.7 should allow abstraction of where data gets stored, but this will of course have serious BW compatibility consequences if not careful.

[lkraav]

If you want to experiment and send a PR I'd love to see that. The CRUD classes in 2.7 should allow abstraction of where data gets stored, but this will of course have serious BW compatibility consequences if not careful.

This is very interesting. If I want to experiment with master on a project, is there any introductory document to get me started with this new technology?

[dima-stefantsov]

Will I really be the first one making such a thread? There's plenty of them. I've read quite few of them.

General contents:
person A: WC EAV is slow
person B,C,D,E: you should know how to cook it (no real links or hints what should be done)
devs: this is wordpress way, nothing will be done (even though it's unusably slow on many products), closed.

Wanna see? Sure, done.

[mikejolley]

@dima-stefantsov The issue you've just raised just reads like a rant and invites "me too" posts which helps no-one.

All that needs to be done is flatten this postmeta EAV hell somehow. Move it to new table wp_woocommerce_product_meta, where it takes 1 row per product, not ~29 in common table.

^ Post things like this. Offer solutions. Contribute. Thats how to get more traction. As a seasoned developer your input would be welcome. To quote myself:

If you want to experiment and send a PR I'd love to see that. The CRUD classes in 2.7 should allow abstraction of where data gets stored, but this will of course have serious BW compatibility consequences if not careful.

We're focusing on CRUD classes and orders right now which are higher priority.

I'll create a new issue with the important parts if you don't edit it when I get a chance. Will be travelling over the next week.

[danielhuesken]

Is it posible to get the order table global on multisites? I think it we can do than some crasy thinks with it like one Invice number circit for invoice plugins or summed up charts for the complete multisite.

[webdados]

Introduce meta setter and getter functions to avoid any kind of get_post_meta usage, where needed.

Any idea on which version will these new functions be available?

[mikejolley]

@webdados 2.7

[webdados]

@mikejolley Nice. I'm looking into my (few) plugins that mess around with order meta and want to make them use these new functions as soon as they exist. (Of course that I will test for WC version >=2.7 and then use them or the "old" update_post_meta and get_post_meta functions).

[UserName011]

It is good that you work on normalized WC database but in my point of view there are errors in the design which will put you in the same place again.

Databases don't like lists. The don't know how to work with them, they can't optimize queries using it, they can't index it. they can do nothing with it.
When you consider optimization you only optimize the PHP side.. you don't consider how the database optimizer works (AKA query planner) for most of WC queries it has to do FULL TABLE SCAN for anything and more than once. This is horrible. And this is why you see more and more topics of people that claim huge slowdown working with giant tables. A properly defined DB can handle millions of records in table... here you get questions for 400 records (see the 400 categories topic).

for example: subscription_status as you define it: 'any', 'active', 'cancelled', 'suspended', 'expired', 'pending' or 'trash'.

Why do you do it like this?
Why not creating a table for subscription_status with status_id and status_name and link this table to whatever table needed it with foreign key ? You should normalize the database as much as possible.

[negrusti]

A sysadmin with ~370 000 orders in WooCommerce here, and about 26 million rows in wp_postmeta.
you can make your bets how long this search for the order by email is executing:

SELECT DISTINCT p1.post_id FROM wp_postmeta p1
INNER JOIN wp_postmeta p2 ON p1.post_id = p2.post_id
WHERE ( p1.meta_key = '_billing_first_name' AND p2.meta_key = '_billing_last_name' AND CONCAT(p1.meta_value, ' ', p2.meta_value) LIKE '%[email protected]%' )
OR ( p1.meta_key = '_shipping_first_name' AND p2.meta_key = '_shipping_last_name' AND CONCAT(p1.meta_value, ' ', p2.meta_value) LIKE '%[email protected]%' )
OR ( p1.meta_key IN ('_order_key','_billing_company','_billing_address_1','_billing_address_2','_billing_city','_billing_postcode','_billing_country','_billing_state','_billing_email','_billing_phone','_shipping_address_1','_shipping_address_2','_shipping_city','_shipping_postcode','_shipping_country','_shipping_state') AND p1.meta_value LIKE '%[email protected]%' )

code is in plugins/woocommerce/includes/wc-order-functions.php

[webdados]

@negrusti Or you can just tell us :-D

[negrusti]

4 min 52 sec for the last run. Of course users could not see the results of this search for a long while already, as it just timeouts for them. As all other aspects of WordPress depend on wp_postmeta, whole site is horrendously slow.

[UserName011]

@negrusti This is exactly what I meant. WooCommerce should not save values in wp_postmeta. Almost anything that has meta_key should be in it's own table.

products table
order table
ordered items table
shipment table
payment table
etc...

[Chunkford]

@UserName011 in an ideal world this would be correct, but Woocommerce is built on top of WP and uses database structures and functions to achieve the desired result, quickly and effectively. Why re-invent the wheel, especially when establishing oneself (which has worked well) to satisfy the majority.
But because of the popularity WC has seen (which wouldn't have happened if it wasn't a plugin for WP), more and more use cases are popping up, pushing the boundaries and hitting limitations that are hampering the progress and development of WC becoming an authority in e-commerce.
This is why orders are being targetted first, as it makes the most logical first step in breaking away from the predefined structure that has currently served WC so well.
I'm sure other areas will be addressed in the future, but for now, let's not complicate matters and start adding feature creep into the mix, it's a huge change as it is.
If this is done right, then I'm sure it will create a path for other areas that need it to follow suit, so don't fret.

[UserName011]

@Chunkford I didn't suggest to normalize the entire Woo Commerce. My suggestion was to perfect the tables which are already touched by this change. You are already separating the orders then there is no reason why not doing it correctly. Your explanation about WordPress did not convince me. No one forces us to use the wp_postmeta to save data. As plugin author you can save it wherever you wish as long as you are supporting the Word Press version.

The suggested solution while is a step forward will not solve cases as described by @negrusti

Please note that you ignored my example in the earlier message:
for example: subscription_status as you define it: 'any', 'active', 'cancelled', 'suspended', 'expired', 'pending' or 'trash'.

This solution will cause problems in the future. There should be no lists in databases. This is a classic case where table is needed. You create something new, there is no backwards comparability. Then why list?

[mikejolley]

Custom tables for order data - performance/scaling

[javorszky]

@mikejolley that card is on a private board it seems, it's not accessible to me (and assume others)

[mikejolley]

@javorszky It's private for now - just experimenting, I'm working on it :) Wasn't aware it was pinging issues.

[lukecav]

@mikejolley Order reports should support CRUD API as well.

[JJJ]

Looks there are still some UI decisions to make. I’d like to suggest keeping the meta-box API, but being very intentional about the individual box designs.

It takes a bit of extra work to use meta-boxes outside of posts, but is worth it for the ease of use.

See: https://github.com/stuttter/wp-user-profiles

Extending into new sections/boxes basically makes the entire setup infinitely awesome, and still sticks to familiar UI conventions so developers and users continue to feel comfortable during the important transition. If y’all want to React’ify it later, you can, or wait until meta-boxes get their own treatment maybe eventually.

[tameemsafi]

UPDATE:

Please see: https://github.com/woocommerce/woocommerce/issues/18378

[pmgarman]

That probably would be better opened as a separate issue.

[tameemsafi]

I have updated my comment above to point to the new issue I created.

[MTphilclothier]

I thought this may be useful to reference here for anyone which has not already seen - https://github.com/liquidweb/woocommerce-order-tables

[mikejolley]

So this issue is out of date now so I wanted to come back and give an update on the status of things.

Phase 1 (Data Abstraction) is complete. We have a robust CRUD system now and data stores to handle data read and writes. Adoption of this is growing and the majority of core now users it where possible. 🎆

Phase 2 (UI) is no longer needed. At least, it is no longer needed in order to move to custom tables. Whilst an eventual order screen redesign is inevitable, CRUD and data stores mean we can implement new tables without doing this first. Keeping the 'post' object around is the path of least resistence.

Phase 3 (Data Migration) is reliant on a feature plugin being shipped and tested first. Liquid Web have their own (https://github.com/liquidweb/woocommerce-custom-orders-table) and @pmgarman requested (https://github.com/woocommerce/woocommerce-order-tables-feature-plugin) but has not yet made movement on this.

@stevegrunwell Seems to be a committer on the Liquid Web one. Steve; if would be good if we could communicate on the plans around that plugin to discuss if a core inclusion is even viable. I'm not sure what you had planned for it.

We'd ideally like to get an 'official' feature plugin setup and distributed, and help whomever works on it get it to the point where it can be rolled into core. We've not had any input in the Liquidweb version because of how it was started. I personally don't mind who we work with but it needs to be open and transparent and we need all contributors to be on the same page, including @pmgarman who has been vocal about it for quite some time.

cc @warrendholmes

So thats the current state of play. Our team is working on product custom tables in the meantime and that will be public soon (and hopefully serve as a template for the path from feature plugin -> core inclusion).

[stevegrunwell]

I may be able to clarify things a bit, here: @pmgarman and the good folks at Mindsize built the first iteration of the Liquid Web plugin, in cooperation with @bswatson, @boogah, @lukecav, and @chrislema on the Liquid Web side.

As that team got swept up in the preparation for Liquid Web's Managed WooCommerce Hosting launch earlier this month, Lema asked that I spend some time focusing on the custom orders table plugin, especially given my propensity for automated testing. As I dug in, I ended up loading the WooCommerce core test suite as a development dependency, ensuring that "if something works in WooCommerce without the plugin, it should still work when the plugin is active."

The maintainers of WooCommerce may have seen a number of PRs come through from me in the past few weeks; these were (mostly) around adding test coverage for areas that touched order data (and thus may need to interface with the custom order table), enabling me to keep that prime directive of "hey, don't break WooCommerce."

From the time I started working on the plugin, I was aware that this discussion was happening and that parts — if not the entire codebase — may be treated as an "official" feature plugin or even rolled into core. As a result, the plugin in its current state has been written to be compatible with PHP 5.2, adhere to WooCommerce's coding standards, and use the same GPL license as WooCommerce core; whatever our plugin can do to further the cause, we're for it :)

That being said, there are certainly portions of the plugin that would need adjustment before the codebase could be rolled into WooCommerce core. For example, the entire customer data store was introduced as a work-around for some hard-coded $wpdb->postmeta table joins in the WC_Customer_Data_Store class. There are also a number of filter callbacks that are performing surgery on WooCommerce-generated SQL queries, which are well-tested but less-than-ideal; rolling the plugin into core would permit some of these replacements to be handled more cleanly.

Short version: this represents @mindsize and @liquidweb going "Yeah, a custom orders table is super useful, and here's a way to do it."

[webdados]

@mikejolley Does this mean that the products will definitely stop being posts, or am I not understanding?
"Our team is working on product custom tables in the meantime and that will be public soon"

[mikejolley]

@webdados They will be posts - but the meta data and attributes will be in a custom table.

@stevegrunwell Thanks @stevegrunwell. Who'd be best to get together to chat about the best path forward? I know @warrendholmes tried reaching out to some folks.

[stevegrunwell]

@mikejolley I'm happy to volunteer from the Liquid Web side, since I've been driving development for the last month or so. I can also pull in others from this side as necessary (right now they're busy giving ❤️ s to my earlier comment).

[webdados]

@mikejolley Nice!

[marcusquinn]

I think some people here and #11467 #11913 #12065 #17241 might find this site and related patch plugins interesting:

800,000 products in Woocommerce searched & filtered in ~0.5s on a $40 Digital Ocean droplet...

• https://foundthru.com/

Should alleviate some misconceptions on the EAV data-model being an issue for performance too ;)

The only thing we want separate order tables for is dev-ops to make the dev > staging > live process easier.

Performance with correct indexes and queries is only as limited as the hardware with this tuned setup.

[pabl0rg]

@surferking i've purchased your scalability pro plugin and it causes problems with wp_sessions

Having proper tables for products and orders would be better than hacking around the EAV data-model.

[kevin25]

Anh ETA for this, @mikejolley ?

[WPprodigy]

See https://github.com/woocommerce/woocommerce/issues/10071#issuecomment-361664197 :)

[jonasskafte]

Thanks @WPprodigy – but @kevin25, did you mean an ETA for the implementation of a data migration solution, which @stevegrunwell was kindly offering to help with here and which @mikejolley is suggesting to move forward with here? -or am I missing something? Anyway, if so, then I'm also super interested! 😃

[kevin25]

@jonasskafte If you don't know right now their plugin doesn't work. I can't even migrate any orders. I need it as the core so it can work stablely.

[NathanBarnard]

Looking forward to this!

[milosh416]

We are going to start year 3 of the wait for the fix that stops the ballooning of order tables. How this massive problem that prevents scalability has not made it a higher on the priority list of the core developers is very questionable.

[alexrowesoap]

I've given up on waiting for this after https://github.com/woocommerce/woocommerce/issues/19937; switched our recommended ecom platform to Sylius for large merchants.

[milosh416]

Who is served by ignoring the issue for 3 years ?

If @mikejolley is running the project, what guides his decision making process on what developers work on ?

In open source transparency goes a long way and this is starting to look like a big corporate game of coming soon in 2016, 2017, 2018, 2019. Give a job to a manager that understands the needs of users and is willing to see the solution completed.

[thisisbbc]

To this day importing and exporting orders still is a nightmare. Wish this was a higher priority.

[pmgarman]

The work done to support this by adding data stores enables developers to put their data wherever they want.

We are now running stores with millions of orders in their databases without the issues we previously experienced previously.

If you are having issues scaling your store I suggest you get data about what is slowing your store down, chances are it is not WC core 9/10 times.

This thread should probably get locked though at this point.

[milosh416]

You have stores running with millions of orders in wp_postmeta ?

[pmgarman]

Hundreds of millions of postmetas in databases. 50-100+ gb in size. Running on our own flavor of auto scaling infrastructure at <$5k/mo driving millions of dollars worth of monthly sales.

I don’t accept that WC can’t scale or drive enterprise e-commerce or high volume e-commerce because we do it regularly.

[crstauf]

The work done to support this by adding data stores enables developers to put their data wherever they want. ...

a.k.a. hire someone to setup custom data stores and tables for you. 👎

@pmgarman why not see the work that's already been done to it's logical conclusion then in core? if all the work for it to happen has been done (which #19937 might suggest it's not been), why stop short?

does someone need to be paid to put this into core? how much? I know it's been done in real-world applications... why hasn't anyone kindly provided their work to be merged into core? why limit it to the big budget projects, or teams with database structure experts? open source seems to lend itself to quality product for the masses, not just those (elites and experts) that know enough and can afford to do things themselves.

I know enough about WooCommerce to customize it, but implementing custom data stores for clients would likely take me 100s of hours, which most clients do not have the budget for.

I get it, the groundwork is there, and the amount of time to implement the custom tables is exhausting; who needs to be paid, and how much, to get this into core for the rest of us devs without expertise, and to the rest of the world?

[pmgarman]

Go look at the LW custom tables plugin, a feature plugin already exists.

Get involved in the existing projects and help continue to test and improve them so someday they may be ready enough for core.

Also yes, I do think that if you are running a high volume or large store then you should have a person or team who can support it properly. A race car can’t succeed without a pit crew to support it.

[milosh416]

Yes we experienced the same, you need $5K per month in CPU power to query a wp_postmeta table with 160 million records > this is the definition of "does not scale"

[crstauf]

@pmgarman ~If I recall, the LW plugin is no longer supported or working properly. Am I incorrect?~ Just checked on the LW plugin, and it does appear to be active: https://github.com/liquidweb/woocommerce-custom-orders-table

I certainly (unfortunately) do not have the expertise to structure a database schema. I'm very willing to test out how I can.~, but as far as I can tell, there are no active projects on this (as indicated by this issue itself).~

I think maintenance and creation are two very different things: I certainly can't build a car, but I can care for its basic needs. That's what most of devs do: we (gratefully) receive the code and product that's been built, and care for its installation on our client's sites. If something minor goes wrong (like shipping methods won't save), we can handle that, but we don't have to worry about WooCommerce processing and storing orders; that functionality is robust and stable. That's all we're asking for: custom database tables implemented to the point where we can care for them.

[crstauf]

@milosh416 I think you may have misread @pmgarman comment: he indicated their site costs less than $5k/month.

[milosh416]

@crstauf They are paid by Automattic, regularly. Such a sad state of affairs.

[crstauf]

@milosh416 My question is rather, who can I (or the community) pay to work on custom order tables for WooCommerce core, and how much would it cost (not by whom are the core lead devs paid).

[milosh416]

It's a lost cause after 3 years ...

[crstauf]

@milosh416 I'm not so sure... this is one of those projects that does take time to implement... the entire first phase has been completed, and the second phase (milestone 3.x.x) seems that it might be underway (?). It is certainly disheartening that we may never see this in WooCommerce core, and especially confusing to see products getting custom database tables ahead of orders, but I think it should be acknowledged that changes of this scale do take time.

And let's not forget: WooCommerce is open source... core devs are not the only ones that move the project forward, and certainly cannot take on every new idea or project. Like I said, I am disheartened by the situation, but hopeful that the LiquidWeb project may be the needed jumpstart into core.

[stevegrunwell]

A custom orders table within WooCommerce is far from a lost cause. Around a year ago, @liquidweb and @mindsize began a collaboration on the WooCommerce Custom Orders Table plugin, which is currently in a Release Candidate state.

This plugin is being built as a drop-in for WooCommerce, making it easy for stores to store common order data in a custom table, taking advantage of the WooCommerce CRUD APIs. We also want to make sure it's easy to migrate existing orders, too, so it ships with a WP-CLI command for automatically migrating orders (or migrating back, if you decide the plugin's not right for you).

As @mikejolley mentioned further up in this thread, the plan is to treat WooCommerce Custom Orders Table as a feature plugin, learn from it, and eventually move towards integrating it into WooCommerce core. In order to do that, we need to get an official 1.0.0 release out, which we're working on (while juggling other projects). If you'd like to help, we'd love to get more people testing the release candidate — moving order data around isn't something we take lightly, so we want to make sure that this is 100% production ready before tagging an official 1.0.0 release.

[crstauf]

Thanks for the update, @stevegrunwell! I for one had thought the LW plugin's development had stalled, so I was surprised (and very glad) to learn that it's moving and in RC! Will definitely set aside some of my personal dev time to testing.

[JJJ]

I invented a relatively sophisticated API for all of the custom database handling we tend to do. This technology was designed to be forked and project agnostic. It will part of Easy Digital Downloads 3.0, Sugar Calendar 2.0, Restrict Content Pro 2.0, and others.

You can find the base of that code here:

https://github.com/easydigitaldownloads/easy-digital-downloads/tree/release/3.0/includes/database

It handles all of the unfun and repetitive database management and registration tasks that come with owning your own storage, including meta-data pairings and caching.

After 13 years of WordPress development, I continue to recommend everyone moves towards this for any application that goes beyond being a blog.

Much like Theme Compatibility in bbPress (around 2010) this set of database APIs was architected as a bit of a hidden gift to other WordPress developers trying to solve the same problems. I hope y'all maybe find it useful in this situation also. 💜

(Ninja Edit: it works really well. There are features I'd like to add, things I'd like to clean up, but time hasn't permitted it on my end.)

[claudiulodro]

This whole thing is not going to be a fast process. Doing it fast would break many stores and would cause much lost revenue for store owners and many problems for plugin developers. The process will span multiple releases and include a lengthy feature-plugin stage to minimize the disruption to users and plugins.

We needed to give the development ecosystem time to stabilize after the initial groundwork laid down for custom tables in the WC 3.0 release, and introducing another giant breaking change shortly after would be counterproductive. Order tables are still on our radar, and improving performance is one of our main priorities for 2019.

Lots of good suggestions for things you can do in the meantime have already been stated here: your own custom data layer, this order tables plugin, this custom indexes plugin should be ready soon-ish, etc.

We'll look into your recommendation @JJJ; thanks for the heads-up. :)

I'm closing comments on this, as it seems we're treading the same conversations over and over again.