Django ORM左连接SQL

eit6fx6z 于 2022-12-20 发布在 Go

关注(0)|答案(3)|浏览(199)

下午好）请告诉我，在django模型的外键中有一个外键，当创建一个连接时，它会在_id数据库中创建一个单元格，然后通过它执行JOIN查询，告诉我如何指定您自己的单元格来执行JOIN，我不能在已经创建的数据库中创建表

I need a banal simple LEFT JOIN without connection with _id.
Or specify another cell in the database for JOIN instead of _id, for example
CastleModels.id = ClanModels.hasCastle

class ClanInfoModels(models.Model):
    clan_id = models.IntegerField()
    name = models.CharField(max_length=80)

    class Meta:
        db_table = 'clan_subpledges'
        managed = False

class ClanModels(models.Model):
    clan_id = models.IntegerField()
    hasCastle = models.IntegerField(primary_key=True)

    class Meta:
        db_table = 'clan_data'
        managed = False

class CastleModels(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=11)

    class Meta:
        db_table = 'castle'
        managed = False
        ordering = ['id']

need sql query =

SELECT castle.name, castle.id, clan_subpledges. name as 'name_clan' FROM castle LEFT JOIN clan_data ON clan_data.hasCastle = castle.id LEFT JOIN clan_subpledges ON clan_subpledges.clan_id = clan_data.clan_id

django

来源：https://stackoverflow.com/questions/74795867/django-orm-left-join-sql

3条答案

按热度按时间

e5njpo681#

只要命名你的领域。

class ClanModels(models.Model):
    clan_id = models.IntegerField()
    hasCastle = models.ForeignField('CastleModels', db_column='hasCastle', on_delete=models.Cascade)

赞(0）回复(0）举报 2022-12-20

biswetbf2#

你可以用Subquery来解决你的问题，但你不应该这样做。我认为你的代码的问题是没有正确定义模型。它应该是这样的：

class ClanInfo(models.Model):
    clan = models.ForeignKey('Clan')
    name = models.CharField(max_length=80)

    class Meta:
        db_table = 'clan_subpledges'
        managed = False

class Clan(models.Model):
    id = models.IntegerField(primary_key=True)
    hasCastle = models.ForeignKey('Castle')

    class Meta:
        db_table = 'clan_data'
        managed = False

class Castle(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=11)

    class Meta:
        db_table = 'castle'
        managed = False
        ordering = ['id']

然后您可以简单地像这样查询：

Castle.objects.values('name', 'id', 'clan__claninfo__name')

要了解反向查询的工作原理，请阅读documentation.FYI：我从每个模型类名中删除了Models，因为您不需要将Models放在类名中，将其标识为模型。
除此之外，如果您坚持保留现有代码，那么下面的代码可能会使用子查询工作：

from django.db.models import OuterRef, Subquery

outer_query = ClanInfo.objects.filter(clan_id=OuterRef('id'))
CastleModels.objects.annotate(clan_subpledges=Subquery(outer_query.values('name')[:1]).values('id', 'name', 'clan_subpledges')

赞(0）回复(0）举报 2022-12-20

k4emjkb13#

利用Django模型定义中的db_column选项。
db_column：用于此字段的数据库列的名称。
您的SQL可以编写为

from django.db.models import F
from .models import CastleModels

queryset = CastleModels.objects.values('name', 'id', name_clan=F('clanmodels__claninfomodels__name'))

我无法完全推断出你的模型设计，但如果
1.一座城堡可以容纳零个或多个氏族，并且

clan_id实际上是部落的主键。
则模型可定义为：

class CastleModels(models.Model):
    id = models.IntegerField(primary_key=True)
    name = models.CharField(max_length=11)

    class Meta:
        db_table = 'castle'
        managed = False
        ordering = ['id']

class ClanModels(models.Model):
    clan_id = models.IntegerField(primary_key=True) 
    hasCastle = models.ForeignKey(CastleModels, on_delete=models.DO_NOTHING, db_column='hasCastle')

    class Meta:
        db_table = 'clan_data'
        managed = False

class ClanInfoModels(models.Model):
    clan_id = models.OneToOneField(ClanModels, on_delete=models.DO_NOTHING, db_column='clan_id')
    name = models.CharField(max_length=80)

    class Meta:
        db_table = 'clan_subpledges'
        managed = False

赞(0）回复(0）举报 2022-12-20

我来回答

Django ORM左连接SQL

3条答案

相关问题

热门标签

最新问答