Join 2 tables in django

Question

Select (..)
FROM ActualTotalLoad 
INNER JOIN ResolutionCode
ON ActualTotalLoad.resolutioncodeid = ResolutionCode.id

I have a uri : /areaname/resolutioncodetext/date

and I want to make the above query, so I can get the resolutioncodetext that is provided from this uri.

I tried Actualtotalload.objects.select_related() but it excludes some columns .

Ask for anything extra you might need

models.py

class Areatypecode(models.Model):
    id = models.AutoField(db_column='Id', primary_key=True)  # Field name made lowercase.
    entitycreatedat = models.DateTimeField(db_column='EntityCreatedAt')  # Field name made lowercase.
    entitymodifiedat = models.DateTimeField(db_column='EntityModifiedAt')  # Field name made lowercase.
    areatypecodetext = models.CharField(db_column='AreaTypeCodeText', max_length=255)  # Field name made lowercase.
    areatypecodenote = models.CharField(db_column='AreaTypeCodeNote', max_length=255)  # Field name made lowercase.

class Actualtotalload(models.Model):
    source = "entso-e"
    dataset ="ActualTotalLoad"
    actualtotalload_id = models.BigAutoField(db_column='Id', primary_key=True)  # Field name made lowercase.
    entitycreatedat = models.DateTimeField(db_column='EntityCreatedAt')  # Field name made lowercase.
    entitymodifiedat = models.DateTimeField(db_column='EntityModifiedAt')  # Field name made lowercase.
    actiontaskid = models.BigIntegerField(db_column='ActionTaskID')  # Field name made lowercase.
    status = models.CharField(db_column='Status', max_length=2, blank=True, null=True)  # Field name made lowercase.
    year = models.IntegerField(db_column='Year')  # Field name made lowercase.
    month = models.IntegerField(db_column='Month')  # Field name made lowercase.
    day = models.IntegerField(db_column='Day')  # Field name made lowercase.
    datetime = models.DateTimeField(db_column='DateTime')  # Field name made lowercase.
    areaname = models.CharField(db_column='AreaName', max_length=200, blank=True, null=True)  # Field name made lowercase.
    updatetime = models.DateTimeField(db_column='UpdateTime')  # Field name made lowercase.
    totalloadvalue = models.DecimalField(db_column='TotalLoadValue', max_digits=24, decimal_places=2)  # Field name made lowercase.
    areatypecodeid = models.ForeignKey(Allocatedeicdetail,db_column='AreaTypeCodeId', on_delete = models.CASCADE, blank=True, null=True)  # Field name made lowercase.
    mapcodeid = models.ForeignKey(Mapcode,on_delete = models.CASCADE, db_column='MapCodeId', blank=True, null=True)  # Field name made lowercase.
    areacodeid = models.ForeignKey(Areatypecode,related_name='areatypecode',on_delete = models.CASCADE, db_column='AreaCodeId')  # Field name made lowercase.
    resolutioncodeid = models.ForeignKey(Resolutioncode,on_delete = models.CASCADE,db_column='ResolutionCodeId', blank=True, null=True)  # Field name made lowercase.
    rowhash = models.CharField(db_column='RowHash', max_length=255, blank=True, null=True)  # Field name made lowercase.

class Resolutioncode(models.Model):
    id = models.AutoField(db_column='Id', primary_key=True)  # Field name made lowercase.
    entitycreatedat = models.DateTimeField(db_column='EntityCreatedAt')  # Field name made lowercase.
    entitymodifiedat = models.DateTimeField(db_column='EntityModifiedAt')  # Field name made lowercase.
    resolutioncodetext = models.CharField(db_column='ResolutionCodeText', unique=True, max_length=255)  # Field name made lowercase.
    resolutioncodenote = models.CharField(db_column='ResolutionCodeNote', max_length=255, blank=True, null=True)  # Field name made lowercase.

The error that I get back from the api :

Exception Value: Cannot resolve keyword 'dataset' into field. Choices are: actiontaskid, actualtotalload_id, areacodeid, areacodeid_id, areaname, areatypecodeid, areatypecodeid_id, datetime, day, entitycreatedat, entitymodifiedat, mapcodeid, mapcodeid_id, month, resolutioncodeid, resolutioncodeid_id, rowhash, status, totalloadvalue, updatetime, year

from django.http import JsonResponse, HttpResponse
from .models import *
from RestApi.serializers import *
# Create your views here.

def ATL_for_date(request , AreaName = None , ResolutionCode = None  , Date = None  ):
    Year = int ( Date [:4])
    Month = int ( Date [5:7])
    Day = int ( Date [8:])
    AreaName = str(AreaName)
    ResolutionCode = str(ResolutionCode)
    Actualtotalload.objects.select_related()
    queryset = list(Actualtotalload.objects.filter( areaname=AreaName ,  resolutioncodeid__resolutioncodetext= ResolutionCode, year = Year , month = Month , day = Day).values('source','dataset','areaname','areatype','mapcode','resolutioncodetext','year','month','day','datetime','totalloadvalue','updatetime'))
    queryset.annotate(dataset=Value('ActualTotalLoad', output_field=CharField()))
    queryset.annotate(source=Value('entso-e', output_field=CharField()))
    return  JsonResponse(queryset, safe = False)

Thank you in advance

alissongranemann · Accepted Answer · 2020-01-15T01:20:00.990

2

You need to put the name of the relation that you want, as an argument :

Actualtotalload.objects.select_related("resolutioncodeid")

And another thing - the id suffix on ForeignKeys is not accurate, because it refers to the model, not the id (Django handles the id, creating a new attribute with the _id suffix.

Edit:

If you filtering by resolutioncodeid__resolutioncodetext, you don't need the select_related('resolutioncodeid').

def ATL_for_date(request, AreaName=None, ResolutionCode=None, Date=None):
    Year = int(Date[:4])
    Month = int(Date[5:7])
    Day = int(Date[8:])
    AreaName = str(AreaName)
    ResolutionCode = str(ResolutionCode)
    queryset = list(
        Actualtotalload.objects.filter(
            areaname=AreaName,
            resolutioncodeid__resolutioncodetext=ResolutionCode,
            year=Year,
            month=Month,
            day=Day,
        )
        .select_related("areatypecodeid", "mapcodeid")
        .values(
            "areaname",
            "areatypecodeid__areatypecodetext",
            "mapcodeid__mapcodetext",
            "resolutioncodeid__resolutioncodetext",
            "year",
            "month",
            "day",
            "datetime",
            "totalloadvalue",
            "updatetime",
        )
    )

    return JsonResponse(queryset, safe=False)

edited Jan 15 '20 at 01:20

answered Jan 15 '20 at 00:22

alissongranemann

964
9
12

Doesn't change the outcome. – JimRoussis Jan 15 '20 at 00:28
Are you using DRF? Could you include the view or the function that contains the query? – alissongranemann Jan 15 '20 at 00:43
2

Yeah, the problem is that `source` and `dataset` are in the `values()` call. They're not being persisted on the database, so you need to remove them from `values()`. If you still want them on the list, you need to iterate through your result and append them, as already mentioned on the other answer. – alissongranemann Jan 15 '20 at 00:59
@alissongranemann following is not an answer as this line does nothing inside of his code, he did not chain it properly nor it is necessary here – iklinac Jan 15 '20 at 01:02
1

@iklinac He put the code after my answer. I'll edit it. – alissongranemann Jan 15 '20 at 01:05
I noticed by printing(str(queryset.query)) that this queryset gives me the query : ```FROM `actualtotalload` INNER JOIN `resolutioncode` ON (`actualtotalload`.`ResolutionCodeId` = `resolutioncode`.`Id`) INNER JOIN `areatypecode` ON (`actualtotalload`.`AreaCodeId` = `areatypecode`.`Id`) LEFT OUTER JOIN `mapcode` ON (`actualtotalload`.`MapCodeId` = `mapcode`.`Id`)``` . I tried to run this query on phpmyadmin and I get an empty set as an answer.... Then I run the query ```FROM `actualtotalload` INNER JOIN `resolutioncode` INNER JOIN `areatypecode` INNER JOIN `mapcode` ```and I got what I wanted – JimRoussis Jan 15 '20 at 08:58
Is there any way to remove the ON field in the FROM clause in django? – JimRoussis Jan 15 '20 at 09:01
1

That doesn't make sense, this will generate a [cross-join](https://stackoverflow.com/a/17759769/10128904), so the result won't be filtered. – alissongranemann Jan 15 '20 at 15:47

score 1 · Answer 2 · answered Jan 15 '20 at 00:59

1

Few things that are wrong:

1) (dataset, source) are not model fields and you cannot use it inside queryset values, you should iterate through your queryset and append these or you could annotate them to queryset for example:

.annotate(dataset=Value('ActualTotalLoad', output_field=CharField()))

2) Following line does nothing as you have already selected following table in values and also you haven't even chained it properly

Actualtotalload.objects.select_related("resolutioncodetext")

answered Jan 15 '20 at 00:59

iklinac

14,944
4
28
30

just added the Areatype table. Im looking for **areatypecodetext** – JimRoussis Jan 15 '20 at 01:10
1

What field do you want from `mapcode` and `areatype`? If you want `mapcodetext` from `mapcode` for example, you need to put the argument as `mapcode__mapcodetext`. – alissongranemann Jan 15 '20 at 01:13
2

You don't have field named areatype in your model, i would suggest you to start with django tutorial to understand how django functions. You probably needed something like areatypecodeid__areaname. – iklinac Jan 15 '20 at 01:14
check my second comment. – JimRoussis Jan 15 '20 at 01:17
2

I reedited my answer, but I agree with @iklinac, you should check Django documentation for basic understanding. – alissongranemann Jan 15 '20 at 01:22

Join 2 tables in django

2 Answers2