将原始sql转换为django查询

Question

如何将此原始sql转换为django查询？？

这是原始查询

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

这是我的知识表：

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)

这是一个有趣的知识：它与表知识有关当用户使用知识时，它会在该表中为用户和知识创建一个新行

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)

我需要将这个原始sql转换为django查询集但我不知道。

Answer 1

尝试以下代码

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