django注释计数过滤器

发布于 2025-01-06 08:42:42 字数 515 浏览 3 评论 0原文

我正在尝试计算某些模型的每日记录，但我希望仅对某些 fk 字段 = xy 的记录进行计数，因此我得到了创建新记录但有些可能返回 0 的天数列表

class SomeModel(models.Model):
    place = models.ForeignKey(Place)
    note = models.TextField()
    time_added = models.DateTimeField()

。名称为“NewYork”的地点

data = SomeModel.objects.extra({'created': "date(time_added)"}).values('created').annotate(placed_in_ny_count=Count('id'))

这有效，但显示所有记录..所有地点。

尝试过过滤，但它不会返回没有 place.name="NewYork" 记录的天数。那不是我需要的。

原文

I am trying to count daily records for some model, but I would like the count was made only for records with some fk field = xy so I get list with days where there was a new record created but some may return 0.

class SomeModel(models.Model):
    place = models.ForeignKey(Place)
    note = models.TextField()
    time_added = models.DateTimeField()

Say There's a Place with name="NewYork"

data = SomeModel.objects.extra({'created': "date(time_added)"}).values('created').annotate(placed_in_ny_count=Count('id'))

This works, but shows all records.. all places.

Tried with filtering, but it does not return days, where there was no record with place.name="NewYork". That's not what I need.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

痴骨ら 2025-01-13 08:42:42

看起来您似乎想知道，对于添加任何对象的每一天，当天创建的对象中有多少个地点的名称为纽约。（如果我误解了，请告诉我。）在需要外连接<的SQL中/a>：

SELECT m.id, date(m.time_added) AS created, count(p.id) AS count
  FROM myapp_somemodel AS m
  LEFT OUTER JOIN myapp_place AS p
       ON m.place_id = p.id
       AND p.name = 'New York'
  GROUP BY created

因此您始终可以使用原始 SQL 查询：

for o in SomeModel.objects.raw('SELECT ...'):   # query as above
    print 'On {0}, {1} objects were added in New York'.format(o.created, o.count)

注释：

我还没有尝试弄清楚这是否可以用 Django 的查询语言表达；可能是，但是正如开发人员所说，数据库 API 是“一条捷径，但不一定是万能的。”）
m.id 是SQL查询中多余的，但是 Django 要求“主键...必须始终包含在原始查询中”。
您可能不想将文字 'New York' 写入查询中，因此传递参数： raw('SELECT ... AND p.name = %s ...', [地名]）。

It looks as though you want to know, for each day on which any object was added, how many of the objects created on that day have a place whose name is New York. (Let me know if I've misunderstood.) In SQL that needs an outer join:

SELECT m.id, date(m.time_added) AS created, count(p.id) AS count
  FROM myapp_somemodel AS m
  LEFT OUTER JOIN myapp_place AS p
       ON m.place_id = p.id
       AND p.name = 'New York'
  GROUP BY created

So you can always express this in Django using a raw SQL query:

for o in SomeModel.objects.raw('SELECT ...'):   # query as above
    print 'On {0}, {1} objects were added in New York'.format(o.created, o.count)

Notes:

I haven't tried to work out if this is expressible in Django's query language; it may be, but as the developers say, the database API is "a shortcut but not necessarily an end-all-be-all.")
The m.id is superfluous in the SQL query, but Django requires that "the primary key ... must always be included in a raw query".
You probably don't want to write the literal 'New York' into your query, so pass a parameter instead: raw('SELECT ... AND p.name = %s ...', [placename]).