Aggregation and count - only counting certain related models? (i.e. adding a filter on the count)

2013-09-24 Thread Victor Hooi 
Hi,

I'm trying to use aggregation to count the number of "likes" on an item.

The likes for an item are stored in a related model, called LikeStatus:

class Item(models.Model):
> ...
> class ItemLikeStatus(models.Model):
> LIKE_STATUS_CHOICES = (
> ('L', 'Liked'),
> ('U', 'Unliked'),
> ('A', 'Abstained (no vote)'),
> ('N', 'Not seen yet'),
> )
> user = models.ForeignKey(User)
> item = models.ForeignKey(Item)
> liked_status = models.CharField(max_length=1, 
> choices=LIKE_STATUS_CHOICES)


I'm using the following aggregation:

items_by_popularity = 
> Item.objects.all().annotate(Count('itemlikestatus')).order_by('-itemlikestatus__count')[:number_requested]


However, I would like to count up only the like statuses where the 
liked_status code is "L".

My thoughts are that I can iterate through the entire Item set myself and 
do a filter on the related itemlikestatus for each Item, and produce the 
aggregation by hand. However, that seems like a silly way to do it, and 
doesn't really use the database.

I was wondering if there's a smarter way to still use aggregation, but 
filter out to only count a subset of related ItemLikeStatus?

Cheers,
Victor

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Aggregation and count - only counting certain related models? (i.e. adding a filter on the count)

2013-09-24 Thread Simon Charette 
Unfortunately the Django ORM's doesn't support conditionnal 
aggregates
.

Le mardi 24 septembre 2013 16:50:51 UTC-4, Victor Hooi a écrit :
>
> Hi,
>
> I'm trying to use aggregation to count the number of "likes" on an item.
>
> The likes for an item are stored in a related model, called LikeStatus:
>
> class Item(models.Model):
>> ...
>> class ItemLikeStatus(models.Model):
>> LIKE_STATUS_CHOICES = (
>> ('L', 'Liked'),
>> ('U', 'Unliked'),
>> ('A', 'Abstained (no vote)'),
>> ('N', 'Not seen yet'),
>> )
>> user = models.ForeignKey(User)
>> item = models.ForeignKey(Item)
>> liked_status = models.CharField(max_length=1, 
>> choices=LIKE_STATUS_CHOICES)
>
>
> I'm using the following aggregation:
>
> items_by_popularity = 
>> Item.objects.all().annotate(Count('itemlikestatus')).order_by('-itemlikestatus__count')[:number_requested]
>
>
> However, I would like to count up only the like statuses where the 
> liked_status code is "L".
>
> My thoughts are that I can iterate through the entire Item set myself and 
> do a filter on the related itemlikestatus for each Item, and produce the 
> aggregation by hand. However, that seems like a silly way to do it, and 
> doesn't really use the database.
>
> I was wondering if there's a smarter way to still use aggregation, but 
> filter out to only count a subset of related ItemLikeStatus?
>
> Cheers,
> Victor
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Aggregation and count - only counting certain related models? (i.e. adding a filter on the count)

2013-09-24 Thread Simon Charette 
Unfortunately the Django ORM's doesn't support conditionnal 
aggregates
.

Le mardi 24 septembre 2013 16:50:51 UTC-4, Victor Hooi a écrit :
>
> Hi,
>
> I'm trying to use aggregation to count the number of "likes" on an item.
>
> The likes for an item are stored in a related model, called LikeStatus:
>
> class Item(models.Model):
>> ...
>> class ItemLikeStatus(models.Model):
>> LIKE_STATUS_CHOICES = (
>> ('L', 'Liked'),
>> ('U', 'Unliked'),
>> ('A', 'Abstained (no vote)'),
>> ('N', 'Not seen yet'),
>> )
>> user = models.ForeignKey(User)
>> item = models.ForeignKey(Item)
>> liked_status = models.CharField(max_length=1, 
>> choices=LIKE_STATUS_CHOICES)
>
>
> I'm using the following aggregation:
>
> items_by_popularity = 
>> Item.objects.all().annotate(Count('itemlikestatus')).order_by('-itemlikestatus__count')[:number_requested]
>
>
> However, I would like to count up only the like statuses where the 
> liked_status code is "L".
>
> My thoughts are that I can iterate through the entire Item set myself and 
> do a filter on the related itemlikestatus for each Item, and produce the 
> aggregation by hand. However, that seems like a silly way to do it, and 
> doesn't really use the database.
>
> I was wondering if there's a smarter way to still use aggregation, but 
> filter out to only count a subset of related ItemLikeStatus?
>
> Cheers,
> Victor
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Aggregation and count - only counting certain related models? (i.e. adding a filter on the count)

2013-09-25 Thread Daniel Roseman 
On Tuesday, 24 September 2013 21:58:44 UTC+1, Simon Charette wrote:

> Unfortunately the Django ORM's doesn't support conditionnal 
> aggregates
> .
>
 
This is true, but completely irrelevant to the OP's question, which doesn't 
require them. The documentation gives an example which AFAICT is exactly 
what the OP wants, 
at 
https://docs.djangoproject.com/en/1.5/topics/db/aggregation/#order-of-annotate-and-filter-clauses.
 
Translating it into the relevant models:

items_by_popularity = 
Item.objects.all().filter(itemlikestatus__liked_status='L').annotate(Count('itemlikestatus')).order_by('-itemlikestatus__count')[:number_requested]

--
DR.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Aggregation and count - only counting certain related models? (i.e. adding a filter on the count)

2013-09-25 Thread Simon Charette 
I assumed the OP wanted to get *all* instances of Item even if they don't 
have any liked status equals to 'L'.

The query you provided will filter out instances of Item with 
`itemlikestatus__count < 1` instead of returning an annotated value of 0.

Le mercredi 25 septembre 2013 04:41:37 UTC-4, Daniel Roseman a écrit :
>
> On Tuesday, 24 September 2013 21:58:44 UTC+1, Simon Charette wrote:
>
>> Unfortunately the Django ORM's doesn't support conditionnal 
>> aggregates
>> .
>>
>  
> This is true, but completely irrelevant to the OP's question, which 
> doesn't require them. The documentation gives an example which AFAICT is 
> exactly what the OP wants, at 
> https://docs.djangoproject.com/en/1.5/topics/db/aggregation/#order-of-annotate-and-filter-clauses.
>  
> Translating it into the relevant models:
>
> items_by_popularity = 
> Item.objects.all().filter(itemlikestatus__liked_status='L').annotate(Count('itemlikestatus')).order_by('-itemlikestatus__count')[:number_requested]
>
> --
> DR.
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.