1052 Column 'product_id' in order clause is ambiguous,

Question

I am trying to run query in Magento 2 Db and getting the error:

Column 'product_id' in order clause is ambiguous,

The query is:

SELECT `main_table`.* FROM `wishlist_item` AS `main_table`
 INNER JOIN `cataloginventory_stock_status` AS `stockItem` 
  ON stockItem.product_id =  main_table.product_id AND stockItem.stock_status = 1
 INNER JOIN `catalog_category_product_index_store1` AS `cat_index` 
  ON cat_index.product_id = main_table.product_id AND cat_index.category_id = '2' 
 AND cat_index.visibility IN (3, 2, 4) 
WHERE (`wishlist_id` = '1') AND (`main_table`.`store_id` IN('1')) 
ORDER BY find_in_set(product_id,'1,2,3'), name ASC
 LIMIT 12

How to fix this?

score 1 · Answer 1 · answered Feb 25 '21 at 16:52

You have columns called product_id in at least two of your tables (in fact you are joining them using columns called product_id) so you need to specify which one you want it to sort by.

It won't infer that the two/more in the join clauses must be identical so it could use either. Be specific and use table_alias.column_name instead of just column_name to remove the ambiguity.

score 0 · Answer 2 · answered Feb 26 '21 at 05:09

David Spillett have explained the root of problem and the way to solving in his answer.

Alternatively, if you want to use product_id without table alias by some exotic reason, you may use USING in joining which collapses joining columns into one column:

SELECT `main_table`.* 
FROM `wishlist_item` AS `main_table`
INNER JOIN `cataloginventory_stock_status` AS `stockItem` USING (product_id)
INNER JOIN `catalog_category_product_index_store1` AS `cat_index` USING (product_id)
WHERE (`wishlist_id` = '1') 
  AND (`main_table`.`store_id` IN('1')) 
  AND stockItem.stock_status = 1
  AND cat_index.category_id = '2' 
  AND cat_index.visibility IN (3, 2, 4) 
ORDER BY find_in_set(product_id,'1,2,3'), name ASC
LIMIT 12

Also you may replace an asterisk in output list with separate columns list - in this case the column name in ORDER BY will be treated as output column alias (if this column name exists in output columnset, of course):

SELECT `main_table`.column1, ... , main_table.product_id, ...
FROM `wishlist_item` AS `main_table`
INNER JOIN `cataloginventory_stock_status` AS `stockItem` 
  ON stockItem.product_id =  main_table.product_id 
  AND stockItem.stock_status = 1
INNER JOIN `catalog_category_product_index_store1` AS `cat_index` 
  ON cat_index.product_id = main_table.product_id 
  AND cat_index.category_id = '2' 
  AND cat_index.visibility IN (3, 2, 4) 
WHERE (`wishlist_id` = '1') 
  AND (`main_table`.`store_id` IN('1')) 
ORDER BY find_in_set(product_id,'1,2,3'), name ASC
LIMIT 12

1052 Column 'product_id' in order clause is ambiguous,

2 Answers2