3.0 Queries: Base query class (meta, dates, caching)

[JJJ]

Tracking issue: #4576

Pull request: #6333

TL;DR

Transition Payments/Discounts/Logs/Notes/Orders/Order-Items post-types to custom component database tables, most paired with meta. Architect a traditional WordPress query interface that's simple and DRY for all of them to maintain developer happiness.

Summary

One technical challenge for EDD3 is the introduction of approximately 12 new database tables (for a total of 14.) The reason for this move is to ultimately provide a more scalable application without a requirement for third-party horizontal scaling techniques (multiple servers; WordPress drop-in plugins; cache/db/lb/web/etc...)

By scaling the databases horizontally by default, EDD can better control the flow of data internally without compromising performance. Put another way, by not storing everything as a custom post type & post-meta, EDD can avoid hurting the performance of other post-type related data, while also improving its own efficiency.

It's a win-win for everyone, but with a hidden caveat...

We learned first-hand (via BuddyPress and Multisite) that the above approach works well-enough at providing a stable foundation for scaling a complex WordPress application, but we also learned having more than a half-dozen different interfaces for interacting with new & unique components in WordPress is a hugely negative experience for traditional WordPress developers.

Each new component/meta relationship is a new hurdle to jump, and too many hurdles leaves developers exhausted and unhappy.

I'm proposing (and working on) a mostly-magic base query-class for all custom database tables to extend, that will automatically make every new table queryable using the same basic parameters, provide object caching for those individual items, without compromising either application performance or developer joy.

Kinda like an ORM, but without the need to relearn the WordPress way of querying for things.

How does it work?

1. Component classes provide an array of column definitions and other component attributes
2. Column definitions help determine the shape of the component item
3. That shape helps define the queryable columns
4. Queries are cached by an md5 hash of parameters used
5. Items are cached by their ID

Components

Here is the master list of components, alphabetically. Each will get checked when complete:

• Customers
• Discounts
• Logs (General)
• Logs (API Requests, No Meta)
• Logs (Downloads, No Meta)
• Notes
• Orders
• Order Items (Linked to Orders)

Orders (Previously Payments)

Table/schema completed (see #6277)

Query vars

• id
• id__in
• id__not_on
• number
• number__in
• number__not_in
• status
• status__in
• status__not_in
• user_id
• user__in
• user__not_in
• customer_id
• customer__in
• customer__not_in
• email
• email__in
• email__not_in
• gateway
• gateway__in
• gateway__not_in
• payment_key
• payment_key__in
• payment_key__not_in
• date_created_query
• date_completed_query
• meta_query

[pippinsplugins]

Add gateway, gateway_in, and gateway__not_in please.

[JJJ]

Add gateway, gateway_in, and gateway__not_in please.

Added.

[cklosowski]

@JJJ looks like gateway came out as gateway_key in the list above, is that intended?

[JJJ]

looks like gateway came out as gateway_key in the list above, is that intended?

Copy/pasta. Fixed.

[JJJ]

Crud; I included a file that was part of a different stash, so I'm gonna clean that up quick.

[JJJ]

OK, here's where this is at now:

• wp_edd_orders has full support for querying single/__in/__not_in most columns
• date_created and date_completed have WP_Date_Query support
• meta_query support is in via WP_Meta_Query
• The base EDD_DB_Query class is the base class that other query classes can extend. It automatically sets up what type of queries are possible based on database column definitions. It's a lot like EDD_DB but on steroids to be more DRY.
• Check out EDD_DB_Order_Query for how sparse each query class will now be, if we choose to go this route. (I think there's opportunity to make this even cleaner once I get the base object classes in.)
• There's a lot to like, and a lot to be critical of, so reviews from @sunnyratilal @cklosowski @pippinsplugins are encouraged. Very much WIP still, emphasis on W.
• This is, basically, a kill 8 birds with 1 stone approach to querying all of our new database tables.

[sunnyratilal]

Closing out as this has been completed. Any bugs found during alpha/beta testing should be opened as new issues.