CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-rest-framework
Jega
Jega

Reputation: 101

Joined table fields in Django-rest-framework

I am into a PHP to Django migration project, where I cannot change the schema of my Database

There are places where multiple joins are there for fields without FK and I have no rights to add them now. Even on my Django models because of the huge size of the DB and time constraint on the project delivery.

I am using Django-rest-framework and overriding the list method and queryset to accept these raw sqls.

sql = """SELECT
  a.field1,
  b.field2
FROM a
LEFT JOIN b ON a.fieldx = b.fieldy"""

The problem is I cannot add the fields of the joined table on my serializer class inside the field tuple because I say model = models.A and this model does not have info about model B.

Your help would be appreciated.

Thank you!

UPDATE I only use viewset. I am not using any view(is this wrong?) My Viewset

class AdminclassinfoViewSet(viewsets.ModelViewSet): 
    queryset = models.Classroominfo.objects.all()
    serializer_class = ClassroominfoSerializer

    def list(self, request):
       sql = """
         SELECT -- DISTINCT 
           cri.classroomid,
           cri.assessmentid, 
           cri.resourceid,
           al.assessmenttype,
           al.assessmenttitle,
           ri.resourcetype,
           ri.resourcetitle,
           wwi.writtenworktitle,
           cri.writtenworkid, 
           si.firstname,
           si.imageurl,
           date(cri.posteddate) as posteddate,
           cri.studentid
       FROM classroominfo cri
       LEFT OUTER JOIN assignassessmentinfo aai ON aai.assessmentid = cri.assessmentid 
                   AND aai.studentid = cri.studentid 
       LEFT OUTER JOIN assessmentlist al ON al.assessmentid = cri.assessmentid 
       LEFT OUTER JOIN assignresourceinfo ari  ON ari.resourceid = cri.resourceid 
                  AND ari.studentid = cri.studentid 
       LEFT OUTER JOIN resourceinfo ri ON ri.resourceid = cri.resourceid
       LEFT OUTER JOIN assignwrittenworkinfo awwi ON awwi.writtenworkid = cri.writtenworkid
       LEFT OUTER JOIN writtenworkinfo wwi ON wwi.writtenworkid = cri.writtenworkid
       LEFT OUTER JOIN logininfo li ON li.loginid = cri.studentid
       LEFT OUTER JOIN studentinfo si ON si.username = li.username
       WHERE ( ari.isclassroom =1 OR 
             aai.isclassroom =1 OR 
            awwi.isclassroom=1 ) 
       ORDER BY cri.classroomid DESC
       """
       queryset = models.Classroominfo.objects.raw(sql)
       serializer_class = ClassroominfoSerializer
       serializer = ClassroominfoSerializer(queryset, many=True)        
       return Response(serializer.data)

My Serializer. I dont know how to refer the joined fields here...

class ClassroominfoSerializer(serializers.ModelSerializer):
    class Meta:
        model = models.Classroominfo
        fields = ('classroomid',  'assessmentid','resourceid',
              'writtenworkid')

Upvotes: 0

Views: 3263

Answers (2)

Jega
Jega

Reputation: 101

Since I am on the deadline, I reverted to run queries directly and converted as a list of dicts and returned from my list method of viewset like this:

desc = cursor.description
result =  [
            dict(zip([col[0] for col in desc], row))
            for row in cursor.fetchall()
    ]
return Response(result)

It still uses the Django Rest API interface so its not a big deal for me to have some occasional hard coded sql queries.

But I would appreciate if Django Rest Framework 3.x being flexible to handle this kinda use cases.

Upvotes: 1

generalpiston
generalpiston

Reputation: 891

So, given your SQL query, it seems like the classroominfo table is at the center of some kind of star schema. You should be able to use the "depth" attribute to include nested serializations.

class ClassroominfoSerializer(serializers.ModelSerializer):
    class Meta:
        model = models.Classroominfo
        fields = ('classroomid',  'assessmentid','resourceid', 'writtenworkid')
        depth = 2

Upvotes: 0

Related Questions