Convert raw sql to django query

Question

How can I convert this raw sql to django query??

this is the raw query


knowledges.raw(
        '''
        SELECT KnowledgeManagement_tblknowledge.KnowledgeCode,KnowledgeManagement_tblknowledge.KnowledgeTitle,KnowledgeManagement_tblknowledge.CreateDate,KnowledgeManagement_tblknowledge.CreatorUserID_id
        FROM KnowledgeManagement_tblknowledge 
        LEFT JOIN KnowledgeManagement_tblusedknowledge ON KnowledgeManagement_tblknowledge.KnowledgeCode = KnowledgeManagement_tblusedknowledge.knowledge_id
        where register_status = 7 or register_status = 9 and Status = 1
        Group by KnowledgeManagement_tblknowledge.KnowledgeCode
        order by count( KnowledgeManagement_tblusedknowledge.id) desc;
        '''
    )

this is my knowledge table:


class TblKnowledge(models.Model):
    KnowledgeCode = models.AutoField(primary_key=True)
    CreatorUserID = models.ForeignKey(
        Members, on_delete=models.PROTECT, null=True, blank=True)

    Experience = 1
    KnowlesgeTypeChoices = (
        (Experience, "تجربه"),
 
    )
    Type = IntegerField('نوع دانش', default=1, choices=KnowlesgeTypeChoices)

    perm_register = 1
    its_knowledge = 7
    rejected_by_security = 8
    accepted_by_security = 9

    status_choices = (
        (perm_register, "(ارسال شده)"),

        (accepted_by_security, "دانش توسط حراست قبول شد"),
    )
    register_status = IntegerField( default=2, choices=status_choices)

    set_number = IntegerField(default=1, null=True, blank=True)

    def __str__(self):
        return str(self.KnowledgeTitle)

And this is tblusedknowledge: it has a relation with table knowledge when a user uses the knowledge it creates a new row in this table for the user and knowledge


class TblUsedKnowledge(models.Model):
    
    creater_user_id = ForeignKey(Members,on_delete=models.PROTECT,blank=True, null=True)
    knowledge = models.ForeignKey(TblKnowledge, on_delete=models.PROTECT, null=True, blank=True)

    def __str__(self):
        return str(self.id)

I need to convert this raw sql to django query-sets but i have no clue.

mostafa hosseini · Answer

Try the following code

    from django.db import connections
    result = {}
    con = connections['default']
    con.ensure_connection()
    cursor = con.connection.cursor(cursor_factory=RealDictCursor)



    cursor.execute("SELECT KnowledgeManagement_tblknowledge.KnowledgeCode,KnowledgeManagement_tblknowledge.KnowledgeTitle,KnowledgeManagement_tblknowledge.CreateDate,KnowledgeManagement_tblknowledge.CreatorUserID_id
    FROM KnowledgeManagement_tblknowledge 
    LEFT JOIN KnowledgeManagement_tblusedknowledge ON KnowledgeManagement_tblknowledge.KnowledgeCode = KnowledgeManagement_tblusedknowledge.knowledge_id
    where register_status = %s or register_status = %s and Status = %s
    Group by KnowledgeManagement_tblknowledge.KnowledgeCode
    order by count( KnowledgeManagement_tblusedknowledge.id) desc;", (7, 9, 1))


    result['Knowledges'] = cursor.fetchall()

ببین کار می کنه

:)

Convert raw sql to django query

1 Answers