SQL error when accessing products in the back end

[fritzmg]

When I try to access the products in the back end as a non-admin, I get the following error:

Doctrine\DBAL\Exception\DriverException:
An exception occurred while executing 'SELECT
                tl_iso_product.*, 'de' AS language, IF(tl_iso_product.pid>0, parent.type, tl_iso_product.type) AS type,
                COUNT(DISTINCT tl_iso_product.id) AS count
            FROM tl_iso_product LEFT OUTER JOIN tl_iso_product_category c ON tl_iso_product.id=c.pid LEFT OUTER JOIN tl_iso_product parent ON tl_iso_product.pid=parent.id WHERE tl_iso_product.language=''':

SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'project.tl_iso_product.id'; this is incompatible with sql_mode=only_full_group_by

  at vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:126
  at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception occurred while executing \'SELECT                tl_iso_product.*, \'de\' AS language, IF(tl_iso_product.pid>0, parent.type, tl_iso_product.type) AS type,                COUNT(DISTINCT tl_iso_product.id) AS count            FROM tl_iso_product LEFT OUTER JOIN tl_iso_product_category c ON tl_iso_product.id=c.pid LEFT OUTER JOIN tl_iso_product parent ON tl_iso_product.pid=parent.id WHERE tl_iso_product.language=\'\'\':SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column \'project.tl_iso_product.id\'; this is incompatible with sql_mode=only_full_group_by', object(PDOException))
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:184)
  at Doctrine\DBAL\DBALException::wrapException(object(Driver), object(PDOException), 'An exception occurred while executing \'SELECT                tl_iso_product.*, \'de\' AS language, IF(tl_iso_product.pid>0, parent.type, tl_iso_product.type) AS type,                COUNT(DISTINCT tl_iso_product.id) AS count            FROM tl_iso_product LEFT OUTER JOIN tl_iso_product_category c ON tl_iso_product.id=c.pid LEFT OUTER JOIN tl_iso_product parent ON tl_iso_product.pid=parent.id WHERE tl_iso_product.language=\'\'\':SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column \'project.tl_iso_product.id\'; this is incompatible with sql_mode=only_full_group_by')
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:158)
  at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'SELECT                tl_iso_product.*, \'de\' AS language, IF(tl_iso_product.pid>0, parent.type, tl_iso_product.type) AS type,                COUNT(DISTINCT tl_iso_product.id) AS count            FROM tl_iso_product LEFT OUTER JOIN tl_iso_product_category c ON tl_iso_product.id=c.pid LEFT OUTER JOIN tl_iso_product parent ON tl_iso_product.pid=parent.id WHERE tl_iso_product.language=\'\'', array())
     (vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:943)
  at Doctrine\DBAL\Connection->executeQuery('SELECT                tl_iso_product.*, \'de\' AS language, IF(tl_iso_product.pid>0, parent.type, tl_iso_product.type) AS type,                COUNT(DISTINCT tl_iso_product.id) AS count            FROM tl_iso_product LEFT OUTER JOIN tl_iso_product_category c ON tl_iso_product.id=c.pid LEFT OUTER JOIN tl_iso_product parent ON tl_iso_product.pid=parent.id WHERE tl_iso_product.language=\'\'')
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:276)
  at Contao\Database\Statement->query()
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Database/Statement.php:250)
  at Contao\Database\Statement->execute(null)
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Model.php:1175)
  at Contao\Model::countBy()
     (vendor/contao/core-bundle/src/Resources/contao/library/Contao/Model.php:1185)
  at Contao\Model::countAll()
     (vendor/isotope/isotope-core/system/modules/isotope/library/Isotope/Backend/Product/Permission.php:199)
  at Isotope\Backend\Product\Permission::getAllowedIds()
     (vendor/isotope/isotope-core/system/modules/isotope/library/Isotope/Backend/Product/Permission.php:53)
  at Isotope\Backend\Product\Permission::check(object(DC_ProductData))
     (vendor/contao/core-bundle/src/Resources/contao/drivers/DC_Table.php:203)
  at Contao\DC_Table->__construct('tl_iso_product')
     (vendor/isotope/isotope-core/system/modules/isotope/drivers/DC_ProductData.php:90)
  at DC_ProductData->__construct('tl_iso_product', array('tables' => array('tl_iso_product', 'tl_iso_group', 'tl_iso_product_category', 'tl_iso_download', 'tl_iso_related_product', 'tl_iso_product_price', 'tl_iso_product_pricetier', 'tl_iso_attribute_option'), 'icon' => 'system/modules/isotope/assets/images/store-open.png', 'javascript' => 'system/modules/isotope/assets/js/backend.js', 'generate' => array('Isotope\\Backend\\Product\\VariantGenerator', 'generate'), 'import' => array('Isotope\\Backend\\Product\\AssetImport', 'generate'), 'fallback' => array('Isotope\\Backend\\Product\\Fallback', 'setFromUrl')))
     (vendor/contao/core-bundle/src/Resources/contao/classes/Backend.php:393)
  at Contao\Backend->getBackendModule('iso_products', null)
     (vendor/contao/core-bundle/src/Resources/contao/controllers/BackendMain.php:163)
  at Contao\BackendMain->run()
     (vendor/contao/core-bundle/src/Controller/BackendController.php:48)
  at Contao\CoreBundle\Controller\BackendController->mainAction()
     (vendor/symfony/http-kernel/HttpKernel.php:149)
  at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), 1)
     (vendor/symfony/http-kernel/HttpKernel.php:66)
  at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), 1, true)
     (vendor/symfony/http-kernel/Kernel.php:188)
  at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
     (web/app_dev.php:84)

It works fine with admin users.

I am not really sure why this join is only done for regular users.

• Contao 4.6.14
• Isotope 2.5.10

[aschempp]

Probably because a permission check is not necessary. Are you running the latest version? Because a group by bug was recently fixed.

[fritzmg]

Yeah, sorry, forgot to add version information. I am running the latest Contao and Isotope versions.

[aschempp]

Fixed in 381fe76