问题描述
这里有三张表ProjectAlgorithmVersion(简称PAV)
和Project
和UserInfo
, 关联关系为:
PAV.project_id = Project.project_id
PAV.create_by = UserInfo.user_id
现在想查询一条PAV
的数据(id=2
), 并查到相关联的Project
表中的Project_name
和UserInfo
表中的user_display_name
, 当使用正常的join时:
query_model = ProjectAlgorithmVersion.select(ProjectAlgorithmVersion.id, Project.project_name, UserInfo.user_display_name).join(Project, on=(ProjectAlgorithmVersion.project_id == Project.project_id)).join(UserInfo, on=(ProjectAlgorithmVersion.create_by ==UserInfo.user_id)).where(ProjectAlgorithmVersion.id == 2)
for result in query_model:
# 查看result的属性
print(result.__dict__)
print('id:', result.id)
# 上面这种join model的写法, 在获取Project表和UserInfo表的数据时, 需要在对应字段前加上类名的小写, 默认小写, 也可以在join(attr=xxx)中新增attr参数, 指定该名称
print('project_name:', result.project.project_name)
print('user_display_name:', result.userinfo.user_display_name)
但是运行结果发现报错: 说 result
没有userinfo
属性, 但是从``result.dict可以看出
project`属性是有的
Traceback (most recent call last):
File "/mnt/d/work/cleartv/01.huarunai/ai/management_cloud/models/model_test.py", line 62, in <module>
sync_fun()
File "/mnt/d/work/cleartv/01.huarunai/ai/management_cloud/models/model_test.py", line 35, in sync_fun
print('user_display_name:', result.userinfo.user_display_name)
AttributeError: 'ProjectAlgorithmVersion' object has no attribute 'userinfo'
{'__data__': {'id': 2}, '_dirty': set(), '__rel__': {}, 'project': <Project: None>}
id: 2
project_name: 上海线上项目01
然后我尝试将两个join的顺序调换, 先join UserInfo
表, 再join Project
表:
query_model = ProjectAlgorithmVersion.select(ProjectAlgorithmVersion.id, Project.project_name, UserInfo.user_display_name).join(UserInfo, on=(ProjectAlgorithmVersion.create_by ==UserInfo.user_id)).join(Project, on=(ProjectAlgorithmVersion.project_id ==Project.project_id)).where(ProjectAlgorithmVersion.id == 2)
发现结果报错变成了没有project
属性:
Traceback (most recent call last):
File "/mnt/d/work/cleartv/01.huarunai/ai/management_cloud/models/model_test.py", line 64, in <module>
sync_fun()
File "/mnt/d/work/cleartv/01.huarunai/ai/management_cloud/models/model_test.py", line 36, in sync_fun
print('project_name:', result.project.project_name)
AttributeError: 'ProjectAlgorithmVersion' object has no attribute 'project'
{'__data__': {'id': 2}, '_dirty': set(), '__rel__': {}, 'userinfo': <UserInfo: None>}
id: 2
猜测peewee如果关联三张表的话, 在查询结果中的__dict__
属性中只能查到第一个join的表, 其他表查不到. 这应该是一个bug
解决
在官网查到另一种join的写法, 可以解决这个问题, 就是在query_model
的最后添加一个.objects()
方法, 加上以后在获取结果的时候就不需要在字段面前加上小写的表名了
query_model = ProjectAlgorithmVersion.select(ProjectAlgorithmVersion.id, Project.project_name, UserInfo.user_display_name).join(Project, on=(ProjectAlgorithmVersion.project_id == Project.project_id)).join(UserInfo, on=(ProjectAlgorithmVersion.create_by ==UserInfo.user_id)).where(ProjectAlgorithmVersion.id == 2)
for result in query_model.objects():
# 查看result的属性
print(result.__dict__)
print('id:', result.id)
# 上面这种join model的写法, 在获取Project表和UserInfo表的数据时, 需要在对应字段前加上属性的小写
print('project_name:', result.project_name)
print('user_display_name:', result.user_display_name)
可以看到执行结果,Project
表和UserInfo
表的数据都能获取到了:
{'__data__': {'id': 2}, '_dirty': set(), '__rel__': {}, 'project_name': '上海线上项目01', 'user_display_name': '龚'}
id: 2
project_name: 上海线上项目01
user_display_name: 龚
补充
- 加上
.objects()
以后query_model
的类型同样是<class 'peewee.ModelSelect'>
, 因此该方法在peewee的异步查询中也可以使用
query_model = ProjectAlgorithmVersion.select(ProjectAlgorithmVersion.version_id, Project.project_name, UserInfo.user_display_name).join(Project, on=(
ProjectAlgorithmVersion.project_id == Project.project_id)).join(UserInfo, on=(
ProjectAlgorithmVersion.create_by == UserInfo.user_id)).where(ProjectAlgorithmVersion.id == 2)
result_list = await ProjectAlgorithmVersion.objects.execute(query_model.objects())
for result in result_list:
print(result.__dict__)
print(result.version_id)
print(result.project_name)
print(result.user_display_name)
-
在
join()
时, 默认情况下获取结果的字段属性的时候, 需要在字段前面加上小写的表模型类名, 如果想自己指定这个名称, 可以在join()
中添加attr=xxx
属性, 这样在获取结果的时候, 可以把小写类名改成xxx- 加之前:
query_model = ProjectAlgorithmVersion.select(ProjectAlgorithmVersion.id, Project.project_name).join(Project, on=(ProjectAlgorithmVersion.project_id == Project.project_id)).where(ProjectAlgorithmVersion.id == 2) for result in query_model: # 查看result的属性 print(result.__dict__) print('id:', result.id) # 使用 .project. print('project_name:', result.project.project_name)
输出结果:
# 输出, 属性为project {'__data__': {'id': 2}, '_dirty': set(), '__rel__': {}, 'project': <Project: None>} id: 2 project_name: 上海线上项目01
- 加
attr='p'
之后:
query_model = ProjectAlgorithmVersion.select(ProjectAlgorithmVersion.id, Project.project_name).join(Project, on=(ProjectAlgorithmVersion.project_id == Project.project_id), attr='p').where(ProjectAlgorithmVersion.id == 2) for result in query_model: # 查看result的属性 print(result.__dict__) print('id:', result.id) # 上面加上attr='p'后, 则使用 .p. print('project_name:', result.project.project_name)
输出结果:
# 输出, 属性为p {'__data__': {'id': 2}, '_dirty': set(), '__rel__': {}, 'p': <Project: None>} id: 2 project_name: 上海线上项目01