Django模型中的多重连接

Question

我有一些Django模型，如下所示：

class Species(models.Model):
    species_id = models.IntegerField(db_column='Species ID', primary_key=True)
    name = models.CharField(db_column='Name', max_length=100)



class Status(models.Model):
    species_id = models.ForeignKey(Species, db_column='Species ID', primary_key=True)
    protected = models.NullBooleanField(db_column='Protected')

class Images(models.Model):
    species_id = models.ForeignKey('Species', db_column='Species ID')
    url = models.CharField(db_column='URL', max_length=719)

有没有什么方法可以在不使用原始SQL的情况下执行以下SQL查询呢？

SELECT Name, URL FROM Images i 
INNER JOIN Species s USING (`Species ID`) 
INNER JOIN Status st USING (`Species ID`) 
WHERE `st`.`Protected` = TRUE

Answer 1

你的数据库结构有点问题。假设每个Species对象只有一个状态，所以你其实不需要三个模型。只用两个模型就可以做到以下这些：

class Species(models.Model):
    species_id = models.IntegerField(db_column='Species ID', primary_key=True)
    name = models.CharField(db_column='Name', max_length=100)
    protected = models.NullBooleanField(db_column='Protected')


class Images(models.Model):
    species = models.ForeignKey('Species', db_column='Species ID')
    url = models.CharField(db_column='URL', max_length=719)

# query
data = Images.objects.filter(species__protected=True).values('url', 'species__name')

Answer 2

如果你稍微调整一下你的模型，你可以这样做：

images = Images.objects.filter(species__status__protected=True)

更新后的模型：

class Species(models.Model):
    name = models.CharField(max_length=100)

class Status(models.Model):
    species = models.ForeignKey('Species', primary_key=True)
    protected = models.NullBooleanField()

class Images(models.Model):
    species = models.ForeignKey('Species')
    url = models.CharField(max_length=719)

Answer 3

这是我会重构你代码的方式：

class Species(models.Model):
    name = models.CharField(db_column='Name', max_length=100)

class Status(models.Model):
    species = models.ForeignKey(Species, db_column='Species ID', primary_key=True)
    protected = models.NullBooleanField(db_column='Protected')

class Images(models.Model):
    species = models.ForeignKey('Species', db_column='Species ID')
    url = models.CharField(db_column='URL', max_length=719)

然后我会这样做：

protected_species = Status.objects
                    .filter(protected=True)
                    .values_list('species_id', flat=True)
Images.objects.filter(species_id__in=protected_species)
              .values('url','species__name')

但是正如@Maxime Lorant所说，我觉得你的模型有点混乱。