2

On my frontend when I type anything in the search box I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'position' in order clause is ambiguous, query was: SELECT `main_table`.`entity_type_id`, `main_table`.`attribute_code`, `main_table`.`attribute_model`, `main_table`.`backend_model`, `main_table`.`backend_type`, `main_table`.`backend_table`, `main_table`.`frontend_model`, `main_table`.`frontend_input`, `main_table`.`frontend_input_renderer`, `main_table`.`frontend_label`, `main_table`.`frontend_class`, `main_table`.`source_model`, `main_table`.`is_global`, `main_table`.`is_visible`, `main_table`.`is_required`, `main_table`.`is_user_defined`, `main_table`.`default_value`, `main_table`.`is_searchable`, `main_table`.`is_filterable`, `main_table`.`is_comparable`, `main_table`.`is_visible_on_front`, `main_table`.`is_html_allowed_on_front`, `main_table`.`is_unique`, `main_table`.`is_configurable`, `main_table`.`apply_to`, `main_table`.`position`, `main_table`.`note`, `main_table`.`is_visible_in_advanced_search`, `main_table`.`is_used_for_price_rules`, `main_table`.`is_filterable_in_search`, `main_table`.`used_in_product_listing`, `main_table`.`used_for_sort_by`, `additional_table`.*, `entity_attribute`.`attribute_id`, IFNULL(al.value, main_table.frontend_label) AS `store_label` FROM `eav_attribute` AS `main_table`
 INNER JOIN `catalog_eav_attribute` AS `additional_table` ON additional_table.attribute_id = main_table.attribute_id
 INNER JOIN `eav_entity_attribute` AS `entity_attribute` ON entity_attribute.attribute_id = main_table.attribute_id
 LEFT JOIN `eav_attribute_label` AS `al` ON al.attribute_id = main_table.attribute_id AND al.store_id = 1 WHERE (main_table.entity_type_id = 4) AND (`entity_attribute`.`attribute_set_id` IN('4')) AND (`additional_table`.`is_filterable` > 0) GROUP BY `entity_attribute`.`attribute_id` ORDER BY position ASC

Now, to overcome this I have amended the getFilterableAttributes() function found in ~/app/code/core/Mage/Catalog/Model/Layer.php to:

     public function getFilterableAttributes()
    {
//        $entity = Mage::getSingleton('eav/config')
//            ->getEntityType('catalog_product');

        $setIds = $this->_getSetIds();
        if (!$setIds) {
            return array();
        }
        /** @var $collection Mage_Catalog_Model_Resource_Product_Attribute_Collection */
        $collection = Mage::getResourceModel('catalog/product_attribute_collection');
        $collection
            ->setItemObjectClass('catalog/resource_eav_attribute')
            ->setAttributeSetFilter($setIds)
            ->addStoreLabel(Mage::app()->getStore()->getId())
            ->setOrder('main_table.position', 'ASC');
        $collection = $this->_prepareAttributeCollection($collection);
        $collection->load();

        return $collection;
    }

This fixes the issue, but now I want to correct the actual problem, I get the error is in laymen terms saying that the position column is present more than once in the query so doesn't know which once to select. But, I cannot see why this is happening. Running the query direct on the database I am able to see position is in the result twice. Would this indicate my database has the position as a duplicate?

Investigating further I found an anomaly, the eav_attribute table seems incorrect, as on a new install I have 17 fields and on my current db I have 33:

New install eav_attribute structure: Fresh install eav_attribute table

Normal installs eav_attribute structure:

Normal install eav_attribute structure 1 Normal install eav_attribute structure 2

  • Magento is a mystery to me. I gave up debugging. I started to trace back what I changed since the last time it was working fine. I won't be much of a help, but is this a custom module or the Mag itself? – Mohammed Joraid Jul 15 '18 at 11:40
  • @MohammedJoraid Magento itself, I believe. As it is a core code issue. – Vaishal Patel Jul 15 '18 at 11:42
  • I saw your update. I'm using 2.2.5 myself so unable to relate. But my thoughts are with you fixing this issue. I'm going to throw some ideas: Could this be related to flat/dynamic catalog? Try to disable all products, search, and enable products again. Do all products cause the search to break? So apparently something happened and changed your eav_attribute table. New extension installed? Configuration/settings changed? Caching system? – Mohammed Joraid Jul 15 '18 at 13:38
  • @MohammedJoraid All good points, it won't be a custom module. – Vaishal Patel Jul 15 '18 at 13:47
  • @MohammedJoraid It is something to do with database :S – Vaishal Patel Jul 15 '18 at 14:21
0

After careful analysis I have arrived at the following: during a previous update one of the scripts was not successful ran.

In the file:

app/code/core/Mage/Catalog/sql/catalog_setup/mysql4-upgrade-0.7.73-1.4.0.0.0.php

You can see the extra fields I have are moved to a new table called catalog_eav_attributes and removed from eav_attributes.

To correct this, I ran the following alter statement on my database:

  ALTER TABLE `eav_attribute`
  DROP `frontend_input_renderer`,
  DROP `is_global`,
  DROP `is_visible`,
  DROP `is_searchable`,
  DROP `is_filterable`,
  DROP `is_comparable`,
  DROP `is_visible_on_front`,
  DROP `is_html_allowed_on_front`,
  DROP `is_configurable`,
  DROP `apply_to`,
  DROP `position`,
  DROP `is_visible_in_advanced_search`,
  DROP `is_used_for_price_rules`,
  DROP `is_filterable_in_search`,
  DROP `used_in_product_listing`,
  DROP `used_for_sort_by`;

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.