This thread is resolved. Here is a description of the problem and solution.
Problem: If you're trying to activate Multicurrency in WooCommerce with the WPML plugin and encounter a WordPress database error stating 'Unknown column 'posts.ID'', this issue typically arises when the SQL query fails to correctly join tables in the database. Solution: We recommend modifying the SQL query used by WooCommerce Multilingual. Here's how you can do it: 1. Navigate to
public function filter_dashboard_status_widget_sales_query( $query ) {
$currency = $this->woocommerce_wpml->multi_currency->admin_currency_selector->get_cookie_dashboard_currency();
if ( isset( $query["select"] ) && ( ( strpos( $query["select"], "SELECT COUNT" ) !== false ) || ( strpos( $query["select"], "SELECT SUM" ) !== false ) ) ) {
$query['join'] .= " INNER JOIN {$this->wpdb->postmeta} AS currency_postmeta ON posts.ID = currency_postmeta.post_id";
} else {
$query['join'] .= " INNER JOIN {$this->wpdb->postmeta} AS currency_postmeta ON orders.ID = currency_postmeta.post_id";
}
$query['where'] .= $this->wpdb->prepare( " AND currency_postmeta.meta_key = '_order_currency' AND currency_postmeta.meta_value = %s", $currency );
return $query;
}
}
This modification should resolve the error. Please note that this solution will be included in a future release of the WCML plugin.
If this solution does not apply to your situation, or if it seems outdated, we highly recommend checking related known issues at https://wpml.org/known-issues/, verifying the version of the permanent fix, and confirming that you have installed the latest versions of themes and plugins. If the issue persists, please open a new support ticket.
This is the technical support forum for WPML - the multilingual WordPress plugin.
Everyone can read, but only WPML clients can post here. WPML team is replying on the forum 6 days per week, 22 hours per day.
Background of the issue:
I am trying to activate Multicurrency in WooCommerce with the WPML plugin. I have the twentytwentyfour theme enabled and only WooCommerce, WPML, WPML String Translation, and WooCommerce Multilingual & Multicurrency plugins active. The issue can be seen at the following link: hidden link. PHP message: WordPress database error Unknown column 'posts.ID'.
Symptoms:
When Multicurrency is activated, I receive a WordPress database error: Unknown column 'posts.ID' in 'on clause' for a specific SQL query. This error occurs while reading the response header from upstream.
Questions:
Why am I receiving a WordPress database error when activating Multicurrency?
How can I resolve the 'Unknown column 'posts.ID'' error?
2024/10/15 14:00:05 [error] 13005#13005: *1796 FastCGI sent in stderr: "PHP message: WordPress database error Unknown column 'posts.ID' in 'on clause' for query SELECT SUM( order_item_meta.meta_value ) as qty, order_item_meta_2.meta_value as product_id FROM wp_posts AS orders INNER JOIN wp_woocommerce_order_items AS order_items ON orders.ID = order_id INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta ON order_items.order_item_id = order_item_meta.order_item_id INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta_2 ON order_items.order_item_id = order_item_meta_2.order_item_id INNER JOIN wp_postmeta AS currency_postmeta ON posts.ID = currency_postmeta.post_id WHERE orders.post_type IN ( 'shop_order' ) AND orders.post_status IN ( 'wc-completed','wc-processing','wc-on-hold' ) AND order_item_meta.meta_key = '_qty' AND order_item_meta_2.meta_key = '_product_id' AND orders.post_date_gmt >= '2024-10-01' AND orders.post_date_gmt <= '2024-10-15 16:00:05' AND currency_postmeta.meta_key = '_order_currency' AND currency_postmeta.meta_value = 'EUR' GROUP BY prod" while reading response header from upstream, client: 77.170.117.85, server: stg-bloomming-staging.kinsta.cloud, request: "GET /wp-admin/index.php HTTP/2.0", upstream: "hidden link:", host: "stg-bloomming-staging.kinsta.cloud:40235", referrer: "hidden link"
Thanks for your advice.
When change the line we get another error:
2024/10/16 07:19:43 [error] 47424#47424: *2193966 FastCGI sent in stderr: "PHP message: WordPress database error Unknown column 'orders.ID' in 'on clause' for query SELECT SUM( order_item_meta__qty.meta_value) as order_item_count FROM wp_posts AS posts INNER JOIN wp_woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id) AND (order_items.order_item_type = 'line_item') INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta__qty ON (order_items.order_item_id = order_item_meta__qty.order_item_id) AND (order_item_meta__qty.meta_key = '_qty') INNER JOIN wp_postmeta AS currency_postmeta ON orders.ID = currency_postmeta.post_id
WHERE posts.post_type IN ( 'shop_order' )
AND posts.post_status IN ( 'wc-refunded')
AND posts.post_date >= '2024-10-01 00:00:00'
AND posts.post_date < '2024-10-17 00:00:00'
AND order_items.order_item_type = 'line_item' AND currency_postmeta.meta_key = '_order_currency' AND currency_postmeta.meta_value = 'EUR' made by wp_dashboard, do_meta_boxes, WC_Admin_Dashboard->status_widget, WC_Admin_Dashbo" while reading response header from upstream, client: 77.170.117.85, server: bloomming.com, request: "GET /wp-admin/index.php HTTP/2.0", upstream: "hidden link:", host: "bloomming.com:34671", referrer: "hidden link"
I've changed the line back to original for now. Please advice. Thanks
I need to request temporary access (wp-admin and FTP) to your site—preferably to a test site where the problem has been replicated if possible—in order to be of better help. When you log in to leave your next reply, you will find the needed fields below the comment area. The information you will enter is private, meaning only you and I can see and access it.
Please provide the steps to replicate the issue.
If I need to check the problem further, I want to ask you about possibly replicating your site locally. I'll need to install a "Duplicator temporarily" or "All In One Migration" plugin on your site. This will allow me to create a copy of your site and your content. If you prefer to do it independently, you can provide the snapshot and send me the zipped package you downloaded.
I couldn't locate /logs -> error.log in the FTP. Additionally, I don't see the error on the website. Can you please provide more details so that I can replicate the error?
Thank you Andrey!
The workaround works and we don't get an error in our log anymore.
Unless we change the currency in our Dashboard widget, we then do receive an error:
2024/10/18 13:34:34 [error] 97995#97995: *2347609 FastCGI sent in stderr: "PHP message: WordPress database error Unknown column 'orders.ID' in 'on clause' for query SELECT order_items.order_item_name as order_item_name,SUM( order_item_meta_discount_amount.meta_value) as discount_amount, posts.post_date as post_date FROM wp_posts AS posts INNER JOIN wp_woocommerce_order_items AS order_items ON (posts.ID = order_items.order_id) AND (order_items.order_item_type = 'coupon') INNER JOIN wp_woocommerce_order_itemmeta AS order_item_meta_discount_amount ON (order_items.order_item_id = order_item_meta_discount_amount.order_item_id) AND (order_item_meta_discount_amount.meta_key = 'discount_amount') INNER JOIN wp_postmeta AS currency_postmeta ON orders.ID = currency_postmeta.post_id
WHERE posts.post_type IN ( 'shop_order' )
AND posts.post_status IN ( 'wc-completed','wc-processing','wc-on-hold','wc-refunded')
AND posts.post_date >= '2024-10-01 00:00:00'
AND posts.post_date < '2024-10-19 00:00:00'
AND order_items.order_item_type = 'coupon' AND curre...; PHP message: WordPress database error Unknown column 'orders.ID' in 'on clause' for query SELECT parent_meta__order_total.meta_value as total_refund, parent_meta__order_shipping.meta_value as total_shipping, parent_meta__order_tax.meta_value as total_tax, parent_meta__order_shipping_tax.meta_value as total_shipping_tax, posts.post_date as post_date FROM wp_posts AS posts INNER JOIN wp_postmeta AS parent_meta__order_total ON (posts.post_parent = parent_meta__order_total.post_id) AND (parent_meta__order_total.meta_key = '_order_total') INNER JOIN wp_postmeta AS parent_meta__order_shipping ON (posts.post_parent = parent_meta__order_shipping.post_id) AND (parent_meta__order_shipping.meta_key = '_order_shipping') INNER JOIN wp_postmeta AS parent_meta__order_tax ON (posts.post_parent = parent_meta__order_tax.post_id) AND (parent_meta__order_tax.meta_key = '_order_tax') INNER JOIN wp_postmeta AS parent_meta__order_shipping_tax ON (posts.post_paren
Thank you for your feedback. I have received confirmation that this error occurred only once during testing and that all cases of the error will be addressed in future releases.
