i have below query and subquery used for ActiveDataProvider.
$subQueryActiveLicenseModules = (new Query())
->select('module_id')
->distinct()
->from('license_module')
->innerJoin('company_license', 'company_license.id = license_module.license_id')
->where('company_license.state=1')
->andWhere('company_license.company_id=:companyId',[':companyId' => Yii::$app->session->get('companyId')]);
$testQuery =RolePermissions::find()
->select(['[module_action].id, [module_action].nameId, [module_action].module_id'])
->addSelect('(CASE
WHEN module_action_description_t.translation is null
THEN module_action.description
ELSE module_action_description_t.translation
END) AS description')
->addSelect('(CASE
WHEN module_action_name_t.translation is null
THEN module_action.name
ELSE module_action_name_t.translation
END) AS name')
->addSelect('(CASE
WHEN module_name_t.translation is null
THEN module.name
ELSE module_name_t.translation
END) AS module_name')
->addSelect('(CASE
WHEN module_description_t.translation is null
THEN module.description
ELSE module_description_t.translation
END) AS module_description')
->addSelect('(CASE
WHEN role_permissions.id is null THEN 0
ELSE 1
END) AS status')
->rightJoin('module_action', 'module_action.id = role_permissions.module_action_id')
->innerJoin('company_roles', ['and','company_roles.id=role_permissions.role_id', 'company_roles.id=:roleId'],[':roleId'=>$id])
->innerJoin('module', 'module.id = module_action.module_id')
->innerJoin(['license_module'=>$subQueryActiveLicenseModules], 'license_module.module_id = module.id')
->leftJoin('module_name_t', ['and','module_name_t.translated_item_id= module.id', 'module_name_t.language_id=:lang'],[':lang'=>Yii::$app->language])
->leftJoin('module_description_t', ['and','module_description_t.translated_item_id= module.id', 'module_description_t.language_id=:lang'],[':lang'=>Yii::$app->language])
->leftJoin('module_action_name_t', ['and','module_action_name_t.translated_item_id= module_action.id', 'module_action_name_t.language_id=:lang'],[':lang'=>Yii::$app->language])
->leftJoin('module_action_description_t', ['and','module_action_description_t.translated_item_id= module_action.id', 'module_action_description_t.language_id=:lang'],[':lang'=>Yii::$app->language]);
Both query’s are runing in sql server but when active data provider try to count the results i get the error.
SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error
The SQL being executed was:
SELECT COUNT([module_action].id)
FROM [role_permissions]
RIGHT JOIN [module_action] ON module_action.id = role_permissions.module_action_id
INNER JOIN [company_roles] ON (company_roles.id=role_permissions.role_id) AND (company_roles.id='4')
INNER JOIN [module] ON module.id = module_action.module_id
INNER JOIN (SELECT DISTINCT [module_id] FROM [license_module]
INNER JOIN [company_license] ON company_license.id = license_module.license_id
WHERE (company_license.state=1) AND (company_license.company_id='56')) [license_module] ON license_module.module_id = module.id
LEFT JOIN [module_name_t] ON (module_name_t.translated_item_id= module.id) AND (module_name_t.language_id='fr')
LEFT JOIN [module_description_t] ON (module_description_t.translated_item_id= module.id) AND (module_description_t.language_id='fr')
LEFT JOIN [module_action_name_t] ON (module_action_name_t.translated_item_id= module_action.id) AND (module_action_name_t.language_id='fr')
LEFT JOIN [module_action_description_t] ON (module_action_description_t.translated_item_id= module_action.id) AND (module_action_description_t.language_id='fr')