Good afternoon) Please tell me, there is a foreign key in django models in the Foreign Key, when creating a connection, it creates a cell in the _id database by which it subsequently performs JOIN queries, tell me how to specify your own cell by which to do JOIN, I can't create tables in an already created database

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

CodePudding user response：

Just name your field.

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

CodePudding user response：

You can use Subquery to solve your problem but you should not. I think the problem with your code is that models are not properly defined. It should be like this:

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']

Then you can simply query like this:

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

To know how the reverse query works, please read the documentation. FYI: I removed Models from every model class name because you do not need to put Models to the class name identify it as a model.

Apart from that, if you insist on keeping your existing code, then the following code might work using subquery:

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')

CodePudding user response：

Take advantage of db_column option in Django model definition.

db_column: The name of the database column to use for this field.

Your SQL can be written as

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

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

I am unable to fully deduce your model design, but if

1. a castle can hold zero or multiple clans, and
2. clan_id is actually the primary key of a clan.

Then the models can be defined as:

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