使用prefetch_related对多个外键进行查询集

Question

以下是我的模型:

class Owner():
    last_name = models.CharField(max_length=30)
    ...
class Species():
    species_code = models.CharField(max_length=10)
    ...
class Pet():
    client = models.ForeignKey(Owner, related_name='pet_fk')
    species = models.ForeignKey(Species)
    ....

我想列出所有主人和他们的宠物。有些主人没有宠物，有些主人有很多宠物。

如果发现宠物，我想在该动物的对象上注释一个额外的"临时"字段css_species_class。如果宠物模型的species_code为"CANINE"，该字段将返回"dog"，如果为"EQUINE"，则返回"horse"，等等。

由于网站是多语言的，需要"临时"字段，并且需要css_species_class值来在模板中拉入适当的字形图标。我不能直接使用存储的值，所以这就是为什么我需要插入一个特定的值来匹配字形所期望的值。

类似:

Owner: John Smith
Pet: Saag (css_species_class='dog')
Pet: Brinjal (css_species_class='cat')
Pet: Baji (css_species_class='dog')
Owner: Sue Smith
Pet: none
Owner: Clare Smith
Pet: Aloo (css_species_class='horse')

模板是这样的:

{% for owner in owners %}
    <tr>
        <td>{{ owner.first_name }} {{ owner.last_name }}</td>
        <td> <!-- loop over pet objects -->
            {% for pet in owner.pet_fk.all %}
                <div>
                    ....
                    <span class="glyphicons glyphicons-{{ pet.css_species_class }}"></span>
                    ....
                </div>
            {% endfor pet %}
        </td>
    </tr>
{% endfor %}

那么，这是我第一次尝试解决方案:

class OwnerListView(ListView):
    template_name = 'visitors/owner_list.html'
    context_object_name = 'owners'
    paginate_by = 50
    def get_queryset(self):
        owners_with_pets = Owner.objects.filter(pet_fk__isnull=False).prefetch_related('pet_fk').distinct()
            # logic goes here to loop over pets
            # and assign 'css_species_class' temp field
        owners_without_pets = Owner.objects.filter(pet_fk__isnull=True).prefetch_related('pet_fk').distinct()

然后将两个查询集"合并"在一起:

        result_list = sorted(
            chain(owners_with_pets, owners_without_pets),
            key=attrgetter('last_name'))
            return result_list

这'工作'为少数所有者，但如果我测试与实数(约4000)我得到'太多的sql变量'错误。

我最初尝试在单个查询中执行此操作(在决定将其分解为两个查询之前)，但是对于大量客户端也失败了。

有人能给我一点指导，如何最好地处理这个问题?非常感谢。

Answer 1

试试这段代码，虽然没有经过测试，但我认为它可以工作。

class Owner():
    last_name = models.CharField(max_length=30)
    ...
class Species():
    species_code = models.CharField(max_length=10)
    ...
class Pet():
    # related_name is used for backward relation
    # this will end up as owner."related_name" --> owner.pets
    client = models.ForeignKey(Owner, related_name='pets')
    species = models.ForeignKey(Species)
    ...
    @property
    def css_species_class(self):
        # this could be anything you want eg: css_scecies_class
        return self.species.species_code

class OwnerListView(ListView):
    template_name = 'visitors/owner_list.html'
    context_object_name = 'owners'
    paginate_by = 50
    def get_queryset(self):
        # no need to check if onwner instance has pets if you chain them both back
        return Owner.objects.prefetch_related('pets').all().distinct()
{% for owner in owners %}
    <tr>
        <td>{{ owner.first_name }} {{ owner.last_name }}</td>
        <td> <!-- loop over pet objects -->
            {% for pet in owner.pets %}
                <div>
                    ....
                    <!-- now we can access pet.css_species_class directly because we made it a property of pet class -->
                    <span class="glyphicons glyphicons-{{ pet.css_species_class }}"></span>
                    ....
                </div>
            {% endfor pet %}
        </td>
    </tr>
{% endfor %}