Django novice question :)
I have the following models - each review is for a product, and each product has a department:
class Department(models.Model):
code = models.CharField(max_length=16)
class Product(models.Model):
id = models.CharField(max_length=40, primary_key=True, db_index=True)
dept = models.ForeignKey(Department, null=True, blank=True, db_index=True)
class Review(models.Model):
review_id = models.CharField(max_length=32, primary_key=True, db_index=True)
product = models.ForeignKey(Product, db_index=True)
time = models.DateTimeField(db_index=True)
I'd like to make a Django query for a date range (2012-01-01 to 2012-01-08) and return a list of all departments, annotated with department ID, and the number of products from that department that were reviewed during that date range.
This is frying my brain a bit :)
I can get all the reviews for a time range:
reviews = Review.filter(time__range=["2012-01-01", "2012-01-08"])
Then I guess each review has a product field, and each of those products has a department code. But how can I group them by product and code, with counts and department IDs?
Alternatively, is it best to request the departments, and then annotate them with product counts, somehow?
Avoid extra and raw whenever possible. The aggregation docs have nearly this use case:
Straight from the docs:
# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
[<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
>>> pubs[0].num_books
73
So, to modify this for your particular example:
depts = Department.objects.
filter(product__review__time__range=["2012-01-01", "2012-01-08"]).
annotate(num_products=Count('product'))
The function calls on separate lines is just for readability and you should move them about accordingly. I haven't tested this, but I think it should work.
I've had to do a couple of similar queries in the last few days and the easiest way it to use the extra queryset function to annotate each object in your queryset with a filtered count of the products:
start = .. # need to be formatted correctly
end = ...
departments = Departments.objects.all().extra(select = {
'product_count' : """ SELECT COUNT(*) FROM appname_department
JOIN appname_product
ON appname_product.dept_id = appname_department.id
JOIN appname_review
ON appname_review.product_id = appname_product.id
WHERE appname_review.time BETWEEN '%s' AND '%s'
""" % (start, end)
})
and
{% for department in departments %}
{{ department.product_count }}
{% endfor %}
The Docs for aggregation https://docs.djangoproject.com/en/dev/topics/db/aggregation/#cheat-sheet
There probably is a way to use aggregate or annotate, but I prefer this:
departments = Department.objects.all()
for dept in departments :
# Get the number of reviewed products for a given range and department
num_products = dept.product_set.filter(review__time__range=["2012-01-01", "2012-01-08"]).count()
if you absolutely need it as a function of the model:
class Department(models.Model) :
...
def num_products(self, start_date, end_date) :
return self.product_set.filter(review__time__range=[start_date, end_date]).count()
EDIT
I think that if you were to do a raw query (something like this)
sql = """SELECT COUNT(Product.*) as num_products, Department.*
FROM Department
LEFT OUTER JOIN Product ON Product.department = Department.id
LEFT OUTER JOIN Review ON Product.id = Review.product
WHERE Review.time BETWEEN "2012-01-01" AND "2012-01-08"
GROUP BY Department.id"""
Department.objects.raw(sql)
and then num_products will be an attribute on every Dept instance in the results.
you may need to play with the field + table names a little
Django novice question :)
I have the following models - each review is for a product, and each product has a department:
class Department(models.Model):
code = models.CharField(max_length=16)
class Product(models.Model):
id = models.CharField(max_length=40, primary_key=True, db_index=True)
dept = models.ForeignKey(Department, null=True, blank=True, db_index=True)
class Review(models.Model):
review_id = models.CharField(max_length=32, primary_key=True, db_index=True)
product = models.ForeignKey(Product, db_index=True)
time = models.DateTimeField(db_index=True)
I'd like to make a Django query for a date range (2012-01-01 to 2012-01-08) and return a list of all departments, annotated with department ID, and the number of products from that department that were reviewed during that date range.
This is frying my brain a bit :)
I can get all the reviews for a time range:
reviews = Review.filter(time__range=["2012-01-01", "2012-01-08"])
Then I guess each review has a product field, and each of those products has a department code. But how can I group them by product and code, with counts and department IDs?
Alternatively, is it best to request the departments, and then annotate them with product counts, somehow?
Avoid extra and raw whenever possible. The aggregation docs have nearly this use case:
Straight from the docs:
# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
[<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
>>> pubs[0].num_books
73
So, to modify this for your particular example:
depts = Department.objects.
filter(product__review__time__range=["2012-01-01", "2012-01-08"]).
annotate(num_products=Count('product'))
The function calls on separate lines is just for readability and you should move them about accordingly. I haven't tested this, but I think it should work.
I've had to do a couple of similar queries in the last few days and the easiest way it to use the extra queryset function to annotate each object in your queryset with a filtered count of the products:
start = .. # need to be formatted correctly
end = ...
departments = Departments.objects.all().extra(select = {
'product_count' : """ SELECT COUNT(*) FROM appname_department
JOIN appname_product
ON appname_product.dept_id = appname_department.id
JOIN appname_review
ON appname_review.product_id = appname_product.id
WHERE appname_review.time BETWEEN '%s' AND '%s'
""" % (start, end)
})
and
{% for department in departments %}
{{ department.product_count }}
{% endfor %}
The Docs for aggregation https://docs.djangoproject.com/en/dev/topics/db/aggregation/#cheat-sheet
There probably is a way to use aggregate or annotate, but I prefer this:
departments = Department.objects.all()
for dept in departments :
# Get the number of reviewed products for a given range and department
num_products = dept.product_set.filter(review__time__range=["2012-01-01", "2012-01-08"]).count()
if you absolutely need it as a function of the model:
class Department(models.Model) :
...
def num_products(self, start_date, end_date) :
return self.product_set.filter(review__time__range=[start_date, end_date]).count()
EDIT
I think that if you were to do a raw query (something like this)
sql = """SELECT COUNT(Product.*) as num_products, Department.*
FROM Department
LEFT OUTER JOIN Product ON Product.department = Department.id
LEFT OUTER JOIN Review ON Product.id = Review.product
WHERE Review.time BETWEEN "2012-01-01" AND "2012-01-08"
GROUP BY Department.id"""
Department.objects.raw(sql)
and then num_products will be an attribute on every Dept instance in the results.
you may need to play with the field + table names a little
0 commentaires:
Enregistrer un commentaire