DB error Unknown column ‘posts.ID’ in ‘on clause’

Hi @nilu1972

Thank you for reaching out — we’re happy to help!

I am seeing this error message in our server error log. Do you have any advice on what is causing this and a possible solution?

The error message “Unknown column ‘posts.ID’ in ‘on clause'” indicates that there is an issue with the SQL query where it tries to reference a column posts.ID that does not exist or is incorrectly referenced. Here’s a breakdown of what might be happening:

Error Analysis

• Unknown Column: The query is attempting to join on posts.ID, but it seems posts is not defined in the query.
• Possible Cause: There might be a missing table in the INNER JOIN clause that should define the posts table.

SQL Query Breakdown

SELECT SUM(order_item_meta.meta_value) as qty, 
       order_item_meta_2.meta_value as product_id 
FROM wp_wc_orders 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.type IN ('shop_order') 
  AND orders.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.date_created_gmt >= '2024-07-01' 
  AND orders.date_created_gmt <= '2024-07-18 08:17:17' 
  AND currency_postmeta.meta_key = '_order_currency' 
  AND currency_postmeta.meta_value = 'EUR' 
GROUP BY product_id 
ORDER BY qty DESC 
LIMIT 1;

Solutions

1. Add the Missing Join: Ensure that the posts table is included in the join statements if currency_postmeta.post_id references posts.ID.
2. Correct Table Reference: Verify if currency_postmeta.post_id should reference a different table that is already included in the joins.

Corrected Query Example

Assuming orders.id should be linked to currency_postmeta.post_id instead of posts.ID:

SELECT SUM(order_item_meta.meta_value) as qty, 
       order_item_meta_2.meta_value as product_id 
FROM wp_wc_orders 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 orders.id = currency_postmeta.post_id 
WHERE orders.type IN ('shop_order') 
  AND orders.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.date_created_gmt >= '2024-07-01' 
  AND orders.date_created_gmt <= '2024-07-18 08:17:17' 
  AND currency_postmeta.meta_key = '_order_currency' 
  AND currency_postmeta.meta_value = 'EUR' 
GROUP BY product_id 
ORDER BY qty DESC 
LIMIT 1;

General Advice

• Check Table Schemas: Ensure the tables and columns referenced in the query exist and are correctly spelled.
• Consult Documentation: Verify the expected table relationships in WooCommerce’s database schema.
• Debug Step-by-Step: Test the query incrementally, adding one join at a time to identify where it fails.

I trust that points you in the right direction, but if you have more questions, let us know.

We’re happy to help.