将SQLQuery转换为Django查询

Question

如何将此查询转换为django queryset？你知道吗

SELECT body, ( SELECT COUNT(status_id) FROM queue WHERE queue.primary_ref_id = messages.Id AND status_id = 2) as count FROM messages

我是django框架的新手。请帮我修一下。。你知道吗

这是我的队列模型：

class Queue(models.Model):
possible_action = models.ForeignKey(PossibleActions)
primary_ref = models.ForeignKey(Message)
secondary_ref = models.ForeignKey(Contacts)
status = models.ForeignKey(Statuses)
created_at = models.DateTimeField(blank=True, null=True)
updated_at = models.DateTimeField()

class Meta:
    managed = False
    db_table = 'queue'

def __str__(self):
    return str(self.possible_action) + str(self.status) + str(self.primary_ref) + str(self.secondary_ref)

这是我的消息模型：

class Message(models.Model):
id = models.AutoField(primary_key=True)
body = models.TextField()
phone_regex = RegexValidator(regex=r'^\+?1?\d{9,15}$',
                             message="Phone number must be entered in the format: '+999999999'. Up to 15 digits allowed.")
cell_number = models.CharField(validators=[phone_regex], max_length=15, blank=True)
received_at = models.DateTimeField(blank=True, null=True)
created_at = models.DateTimeField(blank=True, null=True)
updated_at = models.DateTimeField()
device_id = models.IntegerField()


class Meta:
    managed = False
    db_table = 'messages'

def __unicode__(self):
    return str(self.body) + str(self.cell_number)

更新的解决方案： 以下是我的搜索：

Message.objects.extra(
    select={
        'published_entry_count': "SELECT COUNT(status_id) FROM queue WHERE queue.primary_ref_id = messages.Id AND status_id = 2"
    },
    where=["(SELECT COUNT(status_id) FROM queue WHERE queue.primary_ref_id = messages.Id AND status_id = 2) < 1"]

)

这是我想要的，但问题是，它不会显示我所有的消息。它只显示那些计数为0的。你知道吗

Answer 1

您可以这样用Django编写sql查询

Model.object.raw('SELECT body, ( SELECT COUNT(status_id) FROM queue WHERE queue.primary_ref_id = messages.Id AND status_id = 2) as count FROM messages')