查询的有效逆

Question

我正在尝试寻找最有效的方法遍历我的模型，以获得我所需要的数据。我有三个“相关”模型。Item、ProtectionList和Player

保护列表

class ProtectionList(models.Model):
    player = models.ForeignKey(Player)
    main_hand = models.ForeignKey(Item, null=True, blank=True, related_name='main_hand')
    off_hand = models.ForeignKey(Item, null=True, blank=True, related_name='off_hand')
    head = models.ForeignKey(Item, null=True, blank=True, related_name='head')
    neck = models.ForeignKey(Item, null=True, blank=True, related_name='neck')
    shoulder = models.ForeignKey(Item, null=True, blank=True, related_name='shoulder')
    back = models.ForeignKey(Item, null=True, blank=True, related_name='back')
    chest = models.ForeignKey(Item, null=True, blank=True, related_name='chest')
    wrist = models.ForeignKey(Item, null=True, blank=True, related_name='wrist')
    hands = models.ForeignKey(Item, null=True, blank=True, related_name='hands')
    waist = models.ForeignKey(Item, null=True, blank=True, related_name='waist')
    legs = models.ForeignKey(Item, null=True, blank=True, related_name='legs')
    feet = models.ForeignKey(Item, null=True, blank=True, related_name='feet')
    ring1 = models.ForeignKey(Item, null=True, blank=True, related_name='ring1')
    ring2 = models.ForeignKey(Item, null=True, blank=True, related_name='ring2')
    trinket1 = models.ForeignKey(Item, null=True, blank=True, related_name='trinket1')
    trinket2 = models.ForeignKey(Item, null=True, blank=True, related_name='trinket2')
    locked = models.BooleanField(default=False)

    def __unicode__(self):
        return self.player.main_character.name

玩家

class Player(models.Model):
    accountID = models.CharField(max_length=255, null=True, blank=True)
    battletag = models.CharField(max_length=255, null=True, blank=True)
    user = models.OneToOneField(User, null=True, blank=True)
    main_character = models.ForeignKey('Character', null=True, blank=True, related_name='main_character')
    signature = models.TextField(null=True, blank=True)

    def __unicode__(self):
         return self.battletag

项目

class Item(models.Model):
    '''Details individual data for each item on a loot table'''
    name = models.CharField(max_length=255, null=True)
    item_id = models.IntegerField(null=True)
    dropped_by = models.ForeignKey(Boss)
    warforgeable = models.BooleanField(default=True)
    bonus_string = models.CharField(max_length=255, blank=True, null=True)

    def __unicode__(self):
        return self.name

我需要将我的方法映射回Player，以生成一个Player对象的列表，该列表依赖于谁在其ProtectedList的任何字段中拥有Item。你知道吗

我知道Item与ProtectionList相关，而ProtectionList与Player相关，但是我想找到一种有效的方法来检查Item是ProtectionList对象中的外键的所有字段，因为被过滤的Item可能位于任何字段中（减去player和locked字段）。你知道吗

我已经考虑过使用Q to或每个字段，但我不确定在更大的查询集上这会有多好。你知道吗

我还通过中间表查看，并将模型更改为多对多。你知道吗

Answer 1

我可能错了，但我觉得你的模型结构没有规范化。通过Slots，Player和Item之间存在M2M关系。考虑以下模型结构：

from django.db import models
from django.contrib.auth.models import User

class Slot(models.Model):
    #e.g. main_hand, off_hand, head, ... , feet
    name = models.CharField(max_length=255)

    def __unicode__(self):
        return self.name


class Item(models.Model):
    '''Details individual data for each item on a loot table'''
    name = models.CharField(max_length=255, null=True)
    item_id = models.IntegerField(null=True)
    """removed for the example"""
    #dropped_by = models.ForeignKey(Boss)
    warforgeable = models.BooleanField(default=True)
    bonus_string = models.CharField(max_length=255, blank=True, null=False, default='')

    def __unicode__(self):
        return self.name


class PlayerManager(models.Manager):
    def create_player_with_empty_slots(self, **kwargs):
        player = Player.objects.create(**kwargs)
        PlayerSlot.objects.bulk_create([
            PlayerSlot(player=player, slot=slot) for slot in Slot.objects.all()
        ])
        return player


class Player(models.Model):
    user = models.OneToOneField(User, null=True, blank=True)
    """removed for the example"""
    #accountID = models.CharField(max_length=255, null=True, blank=True)
    #battletag = models.CharField(max_length=255, null=True, blank=True)
    #main_character = models.ForeignKey('Character', null=True, blank=True, related_name='main_character')
    #signature = models.TextField(null=True, blank=True)

    slots = models.ManyToManyField(Slot, through='PlayerSlot')
    items = models.ManyToManyField(Item, through='PlayerSlot')

    objects = PlayerManager()

    def __unicode__(self):
        """changed for the example"""
        return self.user.username

    def get_slot(self, slot_or_slotname):
        if isinstance(slot_or_slotname, Slot):
            return self.playerslot_set.get(slot=slot_or_slotname)
        else:
            return self.playerslot_set.get(slot__name=slot_or_slotname)

    def set_item(self, item, at_slot):
        slot = self.get_slot(at_slot)
        slot.item = item
        slot.save()


class PlayerSlot(models.Model):
    player = models.ForeignKey(Player)
    slot = models.ForeignKey(Slot)
    #item is optional, to allow empty slots
    item = models.ForeignKey(Item, null=True, blank=True)
    locked = models.BooleanField(default=False)

    class Meta:
        unique_together = ('player', 'slot')

    def __unicode__(self):
        return "{player} carry {item} on {slot}".format(player=self.player, item=self.item or 'nothing', slot=self.slot)

使用API的

>>> from django.contrib.auth.models import User
>>> from game.models import Slot, Item, Player, PlayerSlot

让我们创建一些初始数据：

>>> Slot.objects.bulk_create([
...     Slot(name='main_hand'),
...     Slot(name='off_hand'),
...     Slot(name='head'),
...     Slot(name='neck'),
...     Slot(name='shoulder'),
...     Slot(name='back'),
...     Slot(name='chest'),
...     Slot(name='wrist'),
...     Slot(name='hands'),
...     Slot(name='waist'),
...     Slot(name='legs'),
...     Slot(name='feet'),
...     Slot(name='ring1'),
...     Slot(name='ring2'),
...     Slot(name='trinket1'),
...     Slot(name='trinket2'),
... ])
[<Slot: main_hand>, <Slot: off_hand>, <Slot: head>, <Slot: neck>, <Slot: shoulder>, <Slot: back>, <Slot: chest>, <Slot: wrist>, <Slot: hands>, <Slot: waist>, <Slot: legs>, <Slot: feet>, <Slot: ring1>, <Slot: ring2>, <Slot: trinket1>, <Slot: trinket2>]


>>> Item.objects.bulk_create([
...     Item(name='Short Sword'),
...     Item(name='Sabre'),
...     Item(name='Broadsword'),
...     Item(name='Apprentice Broadsword'),
...     Item(name='Monster Hunter'),
... ])
[<Item: Short Sword>, <Item: Sabre>, <Item: Broadsword>, <Item: Apprentice Broadsword>, <Item: Monster Hunter>]


>>> user1 = User.objects.create_user('john', 'lennon@thebeatles.com', 'johnpassword')
>>> user2 = User.objects.create_user('paul', 'mccartney@thebeatles.com', 'mccartnypassword')

>>> player1 = Player.objects.create_player_with_empty_slots(user=user1)
>>> player2 = Player.objects.create_player_with_empty_slots(user=user2)

获取所有玩家位置：

>>> player1.slots.all()
[<Slot: main_hand>, <Slot: off_hand>, <Slot: head>, <Slot: neck>, <Slot: shoulder>, <Slot: back>, <Slot: chest>, <Slot: wrist>, <Slot: hands>, <Slot: waist>, <Slot: legs>, <Slot: feet>, <Slot: ring1>, <Slot: ring2>, <Slot: trinket1>, <Slot: trinket2>]

获取所有玩家物品：

>>> player1.items.all()
[]

添加新项目？你知道吗

>>> broadsword = Item.objects.get(name='Broadsword')
>>> player1.set_item(broadsword, at_slot='main_hand')
>>> player1.items.all()
[<Item: Broadsword>]

检查插槽里有什么？你知道吗

>>> player1.get_slot('main_hand')
<PlayerSlot: john carry Broadsword on main_hand>

查找有物品的玩家：

>>> Player.objects.filter(items=broadsword)
[<Player: john>]

两位炼金术士又举了一个例子：

下面是此模型结构的一些查询示例：

1. 你有一个玩家对象player，需要知道他/她的左手里是什么：

   player.items.filter(slot__name='off_hand')[0].item    # Could be None
   

2. 您有一个Item对象item，希望知道它安装在哪个插槽player：

   PlayerSlot.objects.filter(player=player).filter(item=item)[0].slot.name
   

3. 你有一个物品对象item，你想知道哪些玩家有这个物品：

   PlayerSlot.objects.filter(item=item).all()
   

或者

    Player.objects.filter(slots__item=item).all()

取决于你更愿意使用的对象。我还没有测试上面的任何查询，但这应该让你开始。另外请注意，如果关系没有按您认为的方式排列（例如，player实际上没有item），那么这种方式的链接（查询1&2）可能会抛出IndexError，因此请做好可能捕捉到这种情况的准备。你知道吗

迁移旧数据

我相信像这样的事情可以做到：

plist_slots = [
    'main_hand', 'off_hand', 'head', 'neck', 'shoulder', 
    'back', 'chest', 'wrist', 'hands', 'waist', 'legs',
    'feet', 'ring1', 'ring2', 'trinket1', 'trinket2'
]

Slot.objects.bulk_create([
    Slot(name=slot) for slot in plist_slots
])

plist = ProtectionList.objects.all().select_related(
    *list(['player'] + plist_slots)
)

for p in plist:
    PlayerSlot.objects.bulk_create([
        PlayerSlot(
            player=p.player,
            item=getattr(p, slot),
            slot=Slot.objects.get(name=slot)
        ) for slot in plist_slots
    ])