пытаюсь на сервере выполнить такой запрос с БД
1.
2.
3.
4.
select * from web_users
left join web_access on web_users.web_roles_id = web_access.web_roles_id
left join web_route on web_access.web_route_id = web_route.id
where web_users.id = 1 and web_access.isAccess = 1
у меня есть модели User
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
@Table({ timestamps: false, tableName: "web_users" })
export default class User extends Model {
@AutoIncrement
@Column({ primaryKey: true })
public id: number
@Column({ field: "login" })
public login: string
@Unique
@Column
public email: string
@Column
public password: string
@Column({ field: "FirstName" })
public firstName: string
@Column({ field: "LastName" })
public lastName: string
@Column({ field: "web_roles_id" })
public roleId: number
@HasMany(() => Access)
accesses: Access[]
public validatePassword(password: string) {
return this.password === password.trim()
}
public toPlainObject() {
return {
id: this.id,
login: this.login,
email: this.email,
firstName: this.firstName,
lastName: this.lastName,
roleId: this.roleId
}
}
}
модель Access
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
@Table({ timestamps: false, tableName: "web_access" })
export default class Access extends Model {
@AutoIncrement
@Column({ primaryKey: true })
public id: number
@Column({ field: "web_route_id" })
public routeId: number
@Column({ field: "isAccess" })
public isAccess: boolean
@ForeignKey(() => User)
@Column({ field: "web_roles_id" })
public roleId: number
@BelongsTo(() => User)
user: User
public toPlainObject() {
return {
id: this.id,
roleId: this.roleId,
routeId: this.routeId,
isAccess: this.isAccess
}
}
}
пока пытаюсь связать эти две модели для начала, а потом уже подвязать третью Route. Выполняю запрос
1.
const user = await User.findOne({ where: { email: payload.email.trim() }, include: [Access] })
Далее после авторизации на клиенте, на сервере выполняются такие запросы
1.
2.
3.
Executing (default): SELECT [User].[id], [User].[login], [User].[email], [User].[password], [User].[FirstName] AS [firstName], [User].[LastName] AS [lastName], [User].[web_roles_id] AS [roleId], [accesses].[id] AS [accesses.id], [accesses].[web_route_id] AS [accesses.routeId], [accesses].[isAccess] AS [accesses.isAccess], [accesses].[web_roles_id] AS [accesses.roleId] FROM [web_users] AS [User] LEFT OUTER JOIN [web_access] AS [accesses] ON [User].[id] = [accesses].[web_roles_id] WHERE [User].[email] = N'doc@doc.ua';
Executing (default): SELECT [id], [login], [email], [password], [FirstName] AS [firstName], [LastName] AS [lastName], [web_roles_id] AS [roleId] FROM [web_users] AS [User] WHERE [User].[id] = 2;
Проблема в том, что проверка
1.
LEFT OUTER JOIN [web_access] AS [accesses] ON [User].[id] = [accesses].[web_roles_id]
выполняется не верно, почему-то подставляет
[User].[id] , а должно
[User].[web_roles_id] , как изначально в запросе БД. Делаю как в доке, не пойму почему не получается правильно сделать проверку, перепробовал массу вариантов. Буду очень благодарен за любую помощь!
