Peewee中join三张及以上的表时只能获取一张表的数据

目录

• 问题描述
• 解决
• 补充

问题描述

这里有三张表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