如何在sqlite django ORM中实现having子句 [英] How to implement the having clause in sqlite django ORM

问题描述

我已经编写了django sqlite orm语法来检索特定的记录集：

  from django.db.models.aggregates导入计数
 
 JobStatus.objects.filter（
 status ='PRF'
）.values_list（
'job'，flat = True 
）。 order_by（
'job'
）.aggregate（
计数（状态）__ gt = 3 
）.distinct（）
  pre> 
 
 
但是它给了我一个错误，这个语法的sql相当于对我有用。
 
 
 
是我的sql等价物。
  SELECT * 
 FROM tracker_jobstatus 
 WHERE status ='PRF'
 GROUP BY job_id 
 HAVING COUNT（status）> 3; 
  
我得到的结果如下
  + ---- + -------- + -------- + --------- + ---- ----------------- + --------- + 
 | id | job_id |状态|评论| date_and_time | user_id | 
 + ---- + -------- + -------- + --------- + ------------ --------- + --------- + 
 | 13 | 3 | PRF | | 2012-11-12 13:16:00 | 1 | 
 | 31 | 4 | PRF | | 2012-11-12 13:48:00 | 1 | 
 + ---- + -------- + -------- + --------- + ------------ --------- + --------- + 
  
我'无法找到django sqlite等效于此。 
 
 
 
如果有人能帮忙，我将非常感激。 
解决方案
最后我设法弄清楚了。 ORM语法是这样的。
  from django.db.models.aggregates import Count 
 
 JobStatus.objects.filter（
 status ='PRF'
）.values_list（
'job'，flat = True 
）.order_by（
'job'
）.annotate（
 count_status = Count（'status'）
）.filter（
 count_status__gt = 1 
）.distinct（）
  
 
I've written django sqlite orm syntax to retrieve particular set of records:
from django.db.models.aggregates import Count

JobStatus.objects.filter(
    status='PRF'
).values_list(
    'job', flat=True
).order_by(
    'job'
).aggregate(
    Count(status)__gt=3
).distinct()
But it gives me an error and the sql equivalent for this syntax works fine for me.

This is my sql equivalent.
SELECT *
  FROM tracker_jobstatus
 WHERE status = 'PRF'
 GROUP BY job_id
HAVING COUNT(status) > 3;
and I'm getting the result as follows
+----+--------+--------+---------+---------------------+---------+
| id | job_id | status | comment | date_and_time       | user_id |
+----+--------+--------+---------+---------------------+---------+
| 13 |      3 | PRF    |         | 2012-11-12 13:16:00 |       1 |
| 31 |      4 | PRF    |         | 2012-11-12 13:48:00 |       1 |
+----+--------+--------+---------+---------------------+---------+
I'm unable to find the django sqlite equivalent for this. 

I will be very grateful if anyone can help. 
解决方案
Finally I've managed to figure it out. The ORM syntax is something like this.
from django.db.models.aggregates import Count

JobStatus.objects.filter(
    status='PRF'
).values_list(
    'job', flat=True
).order_by(
    'job'
).annotate(
    count_status=Count('status')
).filter(
    count_status__gt=1
).distinct()


                        
这篇关于如何在sqlite django ORM中实现having子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！