SQL Syntax Error near 'cat_index.is_parent=1'

Question

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.

Answer 1

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';