英文:
Django queryset - fetching reverse relations
问题
以下是您要翻译的代码部分：
我有两个模型，通过`ForeignKey`建立了一对多的关系。在某个阶段，我获得了一个'one'对象的查询集，并希望尽可能高效地检索与它们关联的'many'对象。以下是一个最简示例：class Car(models.Model):name = models.CharField(max_length=100)description = models.CharField(max_length=1000)@classmethoddef search(cls, keyword):return keyword_search_cars(cls, keyword)class Passenger(models.Model):car = models.ForeignKey(Car, on_delete=models.CASCADE)现在我想这样查询这些模型：# 获取匹配项为'truck'的所有乘客cars = Car.search('truck')passengers = cars.passenger_set.all() # 在查询集上不允许这样做！但我能想到的最好的方法是这样的：car_ids = Car.search('truck').values_list('id', flat=True)passengers = Passenger.objects.filter(car_id__in=car_ids)对于包含数百条记录的`cars`查询集，`__in`过滤器似乎是查询数据库的一种愚蠢方法。根据[文档](https://docs.djangoproject.com/en/4.1/ref/models/querysets/#in)，这不会执行SQL `JOIN`，而是编写一个巨大的`WHERE id IN (1, 2, 3, ... )`，这似乎不是最佳选择。是否有人知道更好的方法？目前我的查询需要10+秒（有100万+条记录），这意味着我的应用性能相当差。随着数据库表的增长，它将变得更慢。为了进一步了解情况，这里的`Car.search()`方法可以被视为使用`django.contrib.postgres.search`模块进行全文搜索的抽象（这实际上是我的应用正在做的）。编辑：显示`Car.search()`背后的全文搜索方法的代码：from django.contrib.postgres.search import (SearchQuery,SearchRank,SearchVector,)RANK_THRESHOLD = 0.1def keyword_search_cars(Model, query):vector = (SearchVector('name', weight='A')+ SearchVector('description', weight='B'))query = SearchQuery(query)cars_results = (Model.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=RANK_THRESHOLD).order_by('-rank'))return cars_results
英文:
I have two models with a one-to-many relation through a ForeignKey. At some stage, I have obtained a queryset of the 'one' objects and would like to retrieve, as efficiently as possible, all of the 'many' objects that are associated with them. Here is a minimal example:
class Car(models.Model):name = models.CharField(max_length=100)description = models.CharField(max_length=1000)@classmethoddef search(cls, keyword):return keyword_search_cars(cls, keyword)class Passenger(models.Model):car = models.ForeignKey(Car, on_delete=models.CASCADE)
Now I would like to query the models like so:
# Get all passengers in cars matching term 'truck'cars = Car.search('truck')passengers = cars.passenger_set.all() # Not permitted on querysets!
But the best I can come up with is something like this:
car_ids = Car.search('truck').values_list('id', flat=True)passengers = Passenger.objects.filter(car_id__in=car_ids)
With hundreds of records in the cars queryset, the __in filter seems like a dumb way to query the database. According to the docs, this does not do a SQL JOIN but instead writes a huge WHERE id IN (1, 2, 3, ... ) which does not seem optimal.
Does anyone know of a better approach for this? Currently my queries take 10+ seconds (with 1M+ records) which means rather poor performance for my application. It will get even slower as the database table grows in length.
For further context, the Cars.search method here can be considered an abstraction for full-text search with the django.contrib.postgres.search module (that's what my application is actually doing).
EDIT: show code for full text search method behind Car.search():
from django.contrib.postgres.search import (SearchQuery,SearchRank,SearchVector,)RANK_THRESHOLD = 0.1def keyword_search_cars(Model, query):vector = (SearchVector('name', weight='A')+ SearchVector('description', weight='B'))query = SearchQuery(query)cars_results = (Model.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=RANK_THRESHOLD).order_by('-rank'))return cars_results
答案1
得分: 1
你可以使用Q对象进行后向过滤查找，以在Car模型的两个字段中搜索keyword：
Passenger.objects.filter(Q(car__name__contains=keyword) | Q(car__description__contains=keyword))
英文:
You can use a backward filter lookup with Q objects to search for keyword in both fields of Car model:
Passenger.objects.filter(Q(car__name__contains=keyword) | Q(car__description__contains=keyword))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论