>Flat Catalog Product Function: Problem in Sorting Product

Flat Catalog Product Function: Problem in Sorting Product

Flat Catalog Data is a useful function that helps to store data in Magento backend more orderly with only one table. By default, Magento uses  EAV (Entity-Attribute-Value) model for storing customer, product and category data. These values are stored in various tables depending on types of value (int, varchar, decimal, DateTime…). With Flat Catalog Data function, product attributes will be saved in only one table, which helps to get access to data more quickly. It is called “catalog_product_flat_$i”; $i is store id. This function is recommended for stores which have a large number of SKUs.

However, we found out that there is a problem existing. It appears when you add a new attribute with “Text Field” as Catalog Input Type and “Integer Number” as Input Validation.

Let make a test to compare: Go to Catalog →  Attribute → Manage Attribute → Add a new attribute

Add a new product attribute with the name “sort-by” for example. Choose “Text Field” in Catalog Input Type for Store Owner and “Integer Number” in Input Validation for Store Owner.


Now, see the frontend. Before enabling Flat Catalog Product function, you can see the attribute “sort” is sorted in descending order (19 > 10 >9)

Frontend without Flat Catalog Product

Then enable Flat Catalog Product in the backend.

Go to System → Configuration → Catalog → Catalog → Frontend. Remember that after enabling this function, you need to reindex Product Flat Data.

Enable Flat Catalog Product

Then the frontend will be as below:

Problem in sorting product with Flat Catalog Product

Here, the values are sorted incorrectly (9 > 19 > 10), because they are considered as text rather than a number. Each figure in each number is counted as separated “text.” The automatic system will “read” from the first figure to the following figure.

Take this example to make clear:

Because 9 > 1 => “9” will be considered to be bigger than “19” and “10”.

Because 9 > 0 => “19” will be considered to be bigger than “10.”

So we have the order 9 >19 >10

It is a small problem but may influence your product sorting. If you do not want this, disable Flat Catalog Product function. 

We hope that our warning can prevent you from an unexpected problem. If this blog is useful to you, please Like and Subscribe our blogs to get further knowledge about Magento and apply to your site.

In case you have any further questions, please Contact us to receive the best support. Our support team who is specified in Web Development will always be ready to help you everytime via Skype, Livechat and Email Support.