Fix database issues when ordering the admin subscriptions table by the last order date on HPOS sites

[james-allan]

Fixes https://github.com/woocommerce/woocommerce-subscriptions/issues/4576

Description

There was an issue when ordering the subscriptions list table by the subscriptions last payment date when using HPOS.

After spending a lot of time trying to narrow down the cause of generic database errors I found that it was mostly caused by using strings as table names eg 'wp_wc_orders' vs just wp_wc_orders.

This PR fixes that and makes some additional minor changes to table alias etc.

How to test this PR

1. Make sure HPOS is enabled in WooCommerce > Settings > Advanced > Features
2. Go to the WooCommerce > Subscriptions list table.
3. Attempt to sort by the Last order date column.
4. On trunk you will see no results and the following database error.

WordPress database error Unknown error 1146 for query
SELECT wp_wc_orders.id, COALESCE(lp.last_payment, orders.date_created_gmt, 0) as lp 
FROM wp_wc_orders LEFT JOIN wp_tmp_XIXiw5htnUIQc2hd4Y3scRHmvRS1LQUVX5N3bmdYVRF_lastpayment lp
ON wp_wc_orders.id = lp.id
LEFT JOIN wp_wc_orders orders on wp_wc_orders.parent_order_id = orders.id WHERE 1=1 AND (wp_wc_orders.status IN ('wc-pending','wc-active','wc-on-hold','wc-cancelled','wc-switched','wc-expired','wc-pending-cancel')) AND (wp_wc_orders.type = 'shop_subscription') 
GROUP BY wp_wc_orders.id
ORDER BY CAST(lp AS DATETIME) ASC
LIMIT 0, 20

5. On this branch there should be results ordered correctly by the subscriptions last payment date. Note that subscriptions with no parent or renewal order will be listed first when ordered in ascending order.

Product impact

• [x] Added changelog entry (or does not apply)
• [ ] Will this PR affect WooCommerce Subscriptions? yes/no/tbc, add issue ref
• [ ] Will this PR affect WooCommerce Payments? yes/no/tbc, add issue ref
• [ ] Added deprecated functions, hooks or classes to the spreadsheet

[james-allan]

I still have an error in my log on this branch:

Interesting. I'm not sure why I wasn't getting that issue given I was getting the other errors with table names being treated as strings.

Thanks for the commit. 😄