Unknown column in order

Question

#1054 - Unknown column 'default_ps_products.manufacturer_id' in 'order clause'

Why am I getting the above error with the instruction below, works fine without p in the instruction, and I don't use the order clause?

 SELECT * FROM `default_ps_products` p WHERE p.`manufacturer_id` = 2 

Answer 1

Since you sent a partial request, this was not obvious from the very beginning, but your full request clarifies the situation;

 SELECT * FROM default_ps_products WHERE manufacturer_id=2 ORDER BY 'default_ps_products'.'manufacturer_id' ASC LIMIT 0, 30 

When you add an alias to the default_ps_products table in select, you cannot selectively use an alias only in WHERE , you also need to change ORDER BY to use the same alias. The full query should be in other words:

 SELECT * FROM default_ps_products p WHERE p.manufacturer_id=2 ORDER BY p.'manufacturer_id' ASC LIMIT 0, 30 

Answer 2

To solve this problem, use SELECT p.* FROM instead of SELECT * FROM .

The reason is that phpMyAdmin adds ORDER BY to your query for the first column in the result grid. Due to the alias, the code that does this does not work.

This issue is reproducible on phpMyAdmin 4.0.6. I do not know the status of the last 4.2.5

Answer 3

Open phpmyadmin. Click on the selected database. Now you have a list of all the tables on the right side. Click on the default_ps_products table structure. Now you see its structure. Now go to the SQL tab and execute the query as "SELECT * FROM default_ps_products ORDER BY". After completing this request, now resolve your problem.

Answer 4

Your request is in order. there are no errors executing this request. there is nothing wrong with the request.

SELECT * FROM default_ps_products AS p WHERE p.manufacturer_id = 2 it works fine. :)

Answer 5

when using @Query(nativeQuery = true) , the underscore format should be used, for example, "Sort.by(Sort.Direction.DESC, "update_time")))" , otherwise you should use the camel properties in the entity, for example, "Sort.by(Sort.Direction.DESC, "updateTime")))"