0

One of my webshops had an issue regarding products being shown which are not supposed to be shown. So, a friend of me and I came to a fix. Add code to the observer "catalog_produyct_collection_apply_limitations_after". The following code has been added:

public function catalogProductCollectionApplyLimitationsAfter($observer) {
    $collection = $observer->getCollection();
    $select = $collection->getSelect();
    $from = $select->getPart('from');

    if(array_key_exists('joinCondition',$from['cat_index'])) {
        if(strpos($from['cat_index']['joinCondition'],'is_parent') === false) {
            $from['cat_index']['joinCondition'] = $from['cat_index']['joinCondition'] . 'AND cat_index.is_parent=1';
            $collection->getSelect()->setPart('from', $from);
        }
    }
}

However, after a while this results in an error when visiting the webshop. This error shows up:

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 'cat_index.is_parent=1 LEFT JOIN core_store AS store_index ON store_index.st' at line 3, query was: SELECT COUNT(DISTINCT e.entity_id) FROM catalog_product_flat_3 AS e INNER JOIN report_compared_product_index AS idx_table ON (idx_table.product_id=e.entity_id) AND (idx_table.visitor_id = '5477917') INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=3AND cat_index.is_parent=1 LEFT JOIN core_store AS store_index ON store_index.store_id = idx_table.store_id LEFT JOIN core_store_group AS store_group_index ON store_index.group_id = store_group_index.group_id LEFT JOIN catalog_category_product_index AS store_cat_index ON store_cat_index.product_id = e.entity_id AND store_cat_index.store_id = idx_table.store_id AND store_cat_index.category_id=store_group_index.root_category_id LEFT JOIN enterprise_catalogpermissions_index AS perm ON perm.category_id=cat_index.category_id AND perm.customer_group_id= 0 AND perm.website_id=3 WHERE (e.status = 1) AND (cat_index.visibility IN(3, 2, 4) OR store_cat_index.visibility IN(3, 2, 4)) AND (perm.grant_catalog_category_view != -2 OR perm.grant_catalog_category_view IS NULL) AND (cat_index.store_id=3) AND (cat_index.is_parent=1)

When I save the php file which contains the observer rewrite without any change, the webshop is working again for a short while. I'm not sure what is causing this and why this is happening.

I hope anyone is able to help me out.

0

in this line you need to add a space:

 $from['cat_index']['joinCondition'] = $from['cat_index']['joinCondition'] . 'AND cat_index.is_parent=1';

should be -->

 $from['cat_index']['joinCondition'] = $from['cat_index']['joinCondition'] . ' AND cat_index.is_parent=1';

I think even this would work as well

 $from['cat_index']['joinCondition'] .= ' AND cat_index.is_parent=1';

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.