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
ASstore_index
ON store_index.st' at line 3, query was: SELECT COUNT(DISTINCT e.entity_id) FROMcatalog_product_flat_3
ASe
INNER JOINreport_compared_product_index
ASidx_table
ON (idx_table.product_id=e.entity_id) AND (idx_table.visitor_id = '5477917') INNER JOINcatalog_category_product_index
AScat_index
ON cat_index.product_id=e.entity_id AND cat_index.store_id=3AND cat_index.is_parent=1 LEFT JOINcore_store
ASstore_index
ON store_index.store_id = idx_table.store_id LEFT JOINcore_store_group
ASstore_group_index
ON store_index.group_id = store_group_index.group_id LEFT JOINcatalog_category_product_index
ASstore_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 JOINenterprise_catalogpermissions_index
ASperm
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.