I've created my own module for managing leaflets in home page and i wanted to have a listing with existing leaflets. I've created UI_Component for listing (by Magicento) but when I go to listing page i have an error with SQL syntax.

[2021-05-14 07:50:23] main.CRITICAL: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'order ASC LIMIT 20' at line 1, query was: SELECT `main_table`.* FROM `example_widgets_leaflet` AS `main_table` ORDER BY order ASC LIMIT 20

From error I know it is something about ORDER BY statement. Sorting is made by adding this three lines of code in listing xml file (in column -> data -> config node):

<item name="sorting" xsi:type="string">asc</item>
<item name="sortable" xsi:type="boolean">true</item>
<item name="sortOrder" xsi:type="number">10</item>

Is there any fix for this?

  • What is you'r SQL and Magento version? – Ankit May 14 at 10:39
  • SQL - 5.7.29, Magento - 2.3.4 – LeTeeL May 14 at 10:42

This is due to "order" is a keyword in MySQL and you have used the same word for column name. You should change your table column name to something else. (I see you have used a column order to be ordered in below piece of code)

`main_table` ORDER BY order ASC LIMIT 20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.