How can i execute this InnerJoin Query with ESQ?

Hi Pedro,



Seems like in your snippet you are setting brackets '[]' in wrong order. Systems tries to add column 'SysUserInRole' to the query which does not exist. I assume that you need to change it for the following:

esq.addColumn("=[SysUserInRole:SysUser].SysRole", "Role")



To make it more clear I have put together an example:

 

var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
					rootSchemaName: "SysAdminUnit"
				});
				
				esq.addColumn("Name");
				esq.addColumn("Contact.Name");
				esq.addColumn("=[SysUserInRole:SysRole].SysUser");
				
				var currentUserFilter = this.Terrasoft.createColumnFilterWithParameter(
						this.Terrasoft.ComparisonType.EQUAL,
						"=[SysUserInRole:SysRole].SysUser",
						Terrasoft.SysValue.CURRENT_USER.value);
						
				esq.filters.addItem(currentUserFilter);
				
				esq.getEntityCollection(function(response) {
					debugger;
				}, this);

This example generates following sql query:

 

exec sp_executesql N'
SELECT
	[SysAdminUnit].[Id] [Id],
	[SysAdminUnit].[Name] [Name],
	[Contact].[Name] [Contact.Name],
	[SysUserInRole].[SysUserId] [SysUserInRole.SysUserId],
	[SysUser].[Name] [SysUser.Name]
FROM
	[dbo].[SysAdminUnit] [SysAdminUnit] WITH(NOLOCK)
	LEFT OUTER JOIN [dbo].[Contact] [Contact] WITH(NOLOCK) ON ([Contact].[Id] = [SysAdminUnit].[ContactId])
	LEFT OUTER JOIN [dbo].[SysUserInRole] [SysUserInRole] WITH(NOLOCK) ON ([SysUserInRole].[SysRoleId] = [SysAdminUnit].[Id])
	LEFT OUTER JOIN [dbo].[SysAdminUnit] [SysUser] WITH(NOLOCK) ON ([SysUser].[Id] = [SysUserInRole].[SysUserId])
WHERE
	[SysUserInRole].[SysUserId] = @P1',N'@P1 uniqueidentifier',@P1='7F3B869F-34F3-4F20-AB4D-7480A5FDF647'

Hope it helps!



Regards,

Dmytro