Filter on many attribute does not work correctly, if use for related entity
Case:
I want register volumes, who's register is accessible to user:
const status = Repository('dfx_RegisterVolume')
.attrs('status')
.where('ID', '=', regVolumeID)
.where('registerID.accessUnitIDs', 'in', orgUnitIDs)
.selectScalar()
Attribute dfx_RegisterVolume.registerID
points to dfx_Register
:
{
"name": "registerID",
"caption": "Register",
"dataType": "Entity",
"associatedEntity": "dfx_Register"
},
And attribute dfx_Register.accessUnitIDs
has data type Many
:
{
"name": "accessUnitIDs",
"caption": "Access for units",
"dataType": "Many",
"associatedEntity": "org_unit",
"associationManyData": "dfx_Register_ou"
}
The query generates wrong SQL:
20191118 13311047 ' + App.fetchAllOrExec
20191118 13311047 ' + App.prepareAndRun(conn=main/MSSQL2012)
20191118 13311047 ' DB Prepare 23us SELECT A01.status FROM dfx_RegisterVolume A01 LEFT JOIN dfx_Register A02 ON A02.ID=A01.registerID WHERE A01.ID=? AND EXISTS (SELECT TOP 1 1 FROM dfx_Register_ou A03 WHERE A03.sourceID=A01.id AND A03.destID IN (SELECT * FROM ?)) AND 1=1 AND A01.mi_deleteDate>=?
20191118 13311047 ' debug P1: Int64 3000000019708
20191118 13311047 ' debug P2: Int64Arr(3) [3000000005929,3000000005932,3000000005935]
20191118 13311047 ' debug P3: DateTime 9999-12-31T00:00:00
20191118 13311048 ' SQL 14.27ms SELECT A01.status FROM dfx_RegisterVolume A01 LEFT JOIN dfx_Register A02 ON A02.ID=A01.registerID WHERE A01.ID=? AND EXISTS (SELECT TOP 1 1 FROM dfx_Register_ou A03 WHERE A03.sourceID=A01.id AND A03.destID IN (SELECT * FROM ?)) AND 1=1 AND A01.mi_deleteDate>=?
20191118 13311048 ' - 00.014.828
Note the part: A03.sourceID=A01.id
, this is where it wrong. It must be A03.sourceID=A02.id
, so it treats the many attribute as it is a part of dfx_RegisterVolume
and not related dfx_Register
entity.