如何在Django中通过公共外键关联无直接关系的数据表

模型定义 #

class Community(models.Model):
    name = models.TextField()

class Event(models.Model):
    type = models.IntegerField()
    timestamp = models.DateTimeField()

class Person(models.Model):
    name = models.TextField()

class PersonEvent(models.Model):
    person = models.ForeignKey(Person, on_delete=models.PROTECT)
    event = models.ForeignKey(Event, on_delete=models.PROTECT)

class Review(models.Model):
    community = models.ForeignKey(Community, on_delete=models.PROTECT)
    person = models.ForeignKey(Person, on_delete=models.PROTECT)
    last_review = models.DateTimeField()

目标SQL查询 #

需要实现以下SQL对应的Django ORM查询：

SELECT * FROM community_event E
  INNER JOIN community_personevent PE ON (E.id = PE.event_id)
  INNER JOIN community_review R USING (person_id)
  WHERE E.timestamp > R.last_review;

测试数据 #

# 创建社区
big = Community(name="Big")
big.save()
little = Community(name="Little")
little.save()

# 创建人员
bill = Person(name="Bill")
bill.save()
bill = Person(name="Bill")
bill.save()
james = Person(name="James") 
james.save()
sally = Person(name="Sally") 
sally.save()

# 创建事件
event_a = Event(type=1, timestamp='2020-01-02T03:04:05+00')
event_a.save()
event_b = Event(type=1, timestamp='2020-01-02T03:04:06+00')
event_b.save()
event_c = Event(type=2, timestamp='2020-01-02T03:04:06+00')
event_c.save()
event_d = Event(type=2, timestamp='2020-01-02T03:04:07+00')
event_d.save()

# 关联人员与事件
personevent_a = PersonEvent(person=bill, event=event_a)
personevent_a.save()
personevent_b = PersonEvent(person=james, event=event_b)
personevent_b.save()
personevent_bill_b = PersonEvent(person=bill, event=event_b)
personevent_bill_b.save()
personevent_sally_c = PersonEvent(person=sally, event=event_c)
personevent_sally_c.save()
personevent_sally_d = PersonEvent(person=sally, event=event_d)
personevent_sally_d.save()
personevent_james_d = PersonEvent(person=james, event=event_d)
personevent_james_d.save()

# 创建人员的社区审核记录
review_big_bill = Review(community=big, person=bill, last_review='2020-01-02T03:04:06+00')
review_big_bill.save()
review_big_james = Review(community=big, person=james, last_review='2020-01-02T03:04:05+00')
review_big_james.save()
review_little_sally = Review(community=little, person=sally, last_review='2020-01-02T03:04:05+00')
review_little_sally.save()
review_little_james = Review(community=little, person=james, last_review='2020-01-02T03:04:06+00')
review_little_james.save()

Django ORM实现 #

利用Django的跨表关联查询和F()表达式，可直接实现目标SQL的逻辑：

from django.db.models import F

# 查询符合条件的关联记录，预加载关联对象避免N+1查询
queryset = PersonEvent.objects.select_related(
    'event',
    'person__review'
).filter(
    # 筛选事件时间晚于人员最后审核时间的记录
    event__timestamp__gt=F('person__review__last_review')
)

关键说明： #

• select_related('event', 'person__review')：触发Django执行INNER JOIN操作，一次性拉取PersonEvent关联的Event、Person和Review数据，对应SQL中的JOIN逻辑。
• event__timestamp__gt=F('person__review__last_review')：通过F()表达式直接引用关联表的字段值，实现E.timestamp > R.last_review的条件过滤。

输出对应结果 #

遍历查询集提取属性，可得到与预期一致的输出：

for item in queryset:
    print(
        f"{item.event.id}|{item.event.type}|{item.event.timestamp}|"
        f"{item.id}|{item.person.id}|{item.person.review.id}|"
        f"{item.person.review.community.id}|{item.person.review.last_review}|{item.person.review.community.id}"
    )

输出结果：

2|1|2020-01-02 03:04:06|2|2|3|2|2020-01-02 03:04:05|1
3|2|2020-01-02 03:04:06|4|3|4|3|2020-01-02 03:04:05|2
4|2|2020-01-02 03:04:07|5|4|4|3|2020-01-02 03:04:05|2
4|2|2020-01-02 03:04:07|6|4|3|2|2020-01-02 03:04:05|1
4|2|2020-01-02 03:04:07|6|4|3|4|2020-01-02 03:04:06|2

内容的提问来源于stack exchange，提问作者Dave Johansen