Django query fails with _id that is not used or created or referenced to

user_9454573 0
Viewed 41

I have used the queryset before though this is my first attempt to JOIN tables but it's not working so far. I am using django 3.2 and python 3.8.1

my models.py

class Mainjoinbook(models.Model):
    fullsitename = models.TextField(primary_key=True)
    creationdate = models.DateTimeField()
    entrytypeid = models.BigIntegerField(blank=True, null=True)
    title = models.TextField(blank=True, null=True)
    tickettype = models.TextField(blank=True, null=True)
    ticket = models.TextField(blank=True, null=True)
    status = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'mainlogbook'

class Sitelocation(models.Model):
    site_name = models.TextField(primary_key=True)
    latitude = models.TextField(blank=True, null=True)
    longitude = models.TextField(blank=True, null=True)
    sites = models.ForeignKey(Mainjoinbook, on_delete=models.DO_NOTHING)

    class Meta:
        managed = False
        db_table = 'tblsiteaccess'

I am trying to get all values from both tables joined in my views.py

qrylocations = Sitelocation.objects.select_related('sites').filter(sites__status='OPEN')

this results in this error as that column is created by django but doesn't belong to the table. I still can't workout how to resolve this as I have tried many options but always get in some kind of error and I hope someone can help me to see what I'm doing wrong in joining the tables on the primary keys defined

psycopg2.errors.UndefinedColumn: column tblsiteaccess.sites_id does not exist

the SQL output shown is as below.

output from qrylocations.query

SELECT "tblsiteaccess"."site_name", "tblsiteaccess"."latitude", "tblsiteaccess"."longitude", "tblsiteaccess"."sites_id", "mainlogbook"."fullsitename", "mainlogbook"."log_id", "mainlogbook"."creationdate", "mainlogbook"."entrytypeid", "mainlogbook"."title", "mainlogbook"."tickettype", "mainlogbook"."ticket", "mainlogbook"."status" FROM "tblsiteaccess" INNER JOIN "mainlogbook" ON ("tblsiteaccess"."sites_id" = "mainlogbook"."fullsitename") WHERE "mainlogbook"."status" = OPEN
django-queryset foreign-keys django
1 Answers

A ForeignKey naturally needs a column in the database table. Since site_name itself is the primary key you should use that as a ForeignKey here, infact instead of a ForeignKey this needs to be a OneToOneField [Django docs] since it is also a primary key and needs to be unique:

class Sitelocation(models.Model):
    site_name = models.OneToOneField(
        Mainjoinbook,
        on_delete=models.CASCADE,
        primary_key=True,
        db_column='site_name'
    )
    latitude = models.TextField(blank=True, null=True)
    longitude = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'tblsiteaccess'
user_149918640
Related