Re: How to use extra to count based on the value of a field?
On Mar 16, 2011, at 8:49 AM, Margie Roginski wrote:
> Hmmm ... so I just got back to trying this and I actually don't think
> the annotate call as suggested does what I am looking for. Say I want
> to annotate queues with the number of closed tasks. This call:
>
> Queue.objects.filter(task__status=Task.STATUS_CLOSED).annotate(num_tasks=Count('task'))
>
> finds all queues that have tasks that are closed, but then it
> annotates the queue with the total number of tasks that point to the
> queue, not just the number of closed tasks that point to the queue.
> It seems like I really need something like this (which doesn't exist):
>
> Queue.objects.annotate(num_tasks=Count('task__status=Task.CLOSED_STATUS'))
>
Would something like this work?
Queue.objects.extra(
select =
{ 'num_tasks' = 'SELECT COUNT(*) FROM app_task WHERE
app_task.queue_id = app_queue.id' and app_task.status=%s'),
select_params=(Task.CLOSED_STATUS,)
>
> At a higher level, I am trying to find a way to sort my queues based
> on the number of tasks that point to the queue of a particular status.
> IE, the user would be able to sort their queues based on number of
> open tasks or number of closed tasks. Perhaps there is some other
> approach that I am missing ...
>
> Margie
>
>
>
>
> On Mar 15, 2:43 am, Tom Evans wrote:
>> On Mon, Mar 14, 2011 at 8:57 PM,MargieRoginski
>>
>> wrote:
>>> class Queue(models.Model):
>>> # fields here, not relevant for this discussion
>>
>>> class Task(models.Mode):
>>> queue = models.ForeignKey("Queue")
>>> status = models.IntegerField(choices=STATUS_CHOICES)
>>
>>> I am trying to create a Queue queryset that willannotateeach Queue
>>> with the number of tasks whose queue field is pointing to that Queue
>>> and status field has a certain value.
>>
>>> I don't thinkannotatewill work for me due to me needing to count up
>>> only tasks whose status field has a certain value. I think I might
>>> need extra? But I'm having touble making that work.
>>
>> No, this is precisely whatannotateis for.
>>
>> Queue.objects.filter(task__status=Task.SOME_CHOICE).annotate(num_tasks=Count('task'))
>>
>> Cheers
>>
>> Tom
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: How to use extra to count based on the value of a field?
Right again! I agree what you are saying works. I tried to take it a
step further and OR together two querysets. I was then looking at the
results of that OR, and it was not what I expected. One qs had been
annotated with num_tasks_open, the other had been annotated with
num_tasks_closed. I was thinking I could just OR them together and
get a single queryset where each queue would would be annotated with
num_tasks_open and num_tasks_closed. But that does not seem to work.
> qsOpen = Queue.objects.filter(name="foo",
> task__status=Task.OPEN_STATUS).annotate(num_tasks_open=Count('task'))
> qsClosed = Queue.objects.filter(name="foo",
> task__status=Task.CLOSED_STATUS).annotate(num_tasks_closed=Count('task'))
> qsOpen[0].num_tasks_open
Out[37]: 1
> qsClosed[0].num_tasks_closed
Out[38]: 4
> newQs = qsOpen | qsClosed
> newQs[0].num_tasks_open <<== Was expecting this to still show 1 task open
Out[41]: 5
> newQs[0].num_tasks_closed <<== Was expecting this to show 4 tasks open
---
AttributeErrorTraceback (most recent call
last)
/home/mlevine/django/chipvision74/chip_vision_2/ in
()
AttributeError: 'Queue' object has no attribute 'num_tasks_closed'
Margie
On Mar 16, 9:13 am, Tom Evans wrote:
> On Wed, Mar 16, 2011 at 3:49 PM, Margie Roginski
>
> wrote:
> > Hmmm ... so I just got back to trying this and I actually don't think
> > the annotate call as suggested does what I am looking for. Say I want
> > to annotate queues with the number of closed tasks. This call:
>
> > Queue.objects.filter(task__status=Task.STATUS_CLOSED).annotate(num_tasks=Count('task'))
>
> > finds all queues that have tasks that are closed, but then it
> > annotates the queue with the total number of tasks that point to the
> > queue, not just the number of closed tasks that point to the queue.
>
> No, you are incorrect. An example with similar models:
>
> >>> qs = TVSeries.objects.filter(name='South
> >>> Park').filter(tvepisode__title__contains='hero').annotate(num_episodes_with_hero_in_title=Count('tvepisode'))
> >>> qs[0].num_episodes_with_hero_in_title
> 1
> >>> qs2 = TVSeries.objects.filter(name='South
> >>> Park').annotate(num_episodes=Count('tvepisode'))
> >>> qs2[0].num_episodes
>
> 202
>
> As you can see, the annotate is clearly correctly affected by the
> earlier filter.
>
> Cheers
>
> Tom
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: How to use extra to count based on the value of a field?
On Wed, Mar 16, 2011 at 3:49 PM, Margie Roginski
wrote:
> Hmmm ... so I just got back to trying this and I actually don't think
> the annotate call as suggested does what I am looking for. Say I want
> to annotate queues with the number of closed tasks. This call:
>
> Queue.objects.filter(task__status=Task.STATUS_CLOSED).annotate(num_tasks=Count('task'))
>
> finds all queues that have tasks that are closed, but then it
> annotates the queue with the total number of tasks that point to the
> queue, not just the number of closed tasks that point to the queue.
No, you are incorrect. An example with similar models:
>>> qs = TVSeries.objects.filter(name='South
>>> Park').filter(tvepisode__title__contains='hero').annotate(num_episodes_with_hero_in_title=Count('tvepisode'))
>>> qs[0].num_episodes_with_hero_in_title
1
>>> qs2 = TVSeries.objects.filter(name='South
>>> Park').annotate(num_episodes=Count('tvepisode'))
>>> qs2[0].num_episodes
202
As you can see, the annotate is clearly correctly affected by the
earlier filter.
Cheers
Tom
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: How to use extra to count based on the value of a field?
Hmmm ... so I just got back to trying this and I actually don't think
the annotate call as suggested does what I am looking for. Say I want
to annotate queues with the number of closed tasks. This call:
Queue.objects.filter(task__status=Task.STATUS_CLOSED).annotate(num_tasks=Count('task'))
finds all queues that have tasks that are closed, but then it
annotates the queue with the total number of tasks that point to the
queue, not just the number of closed tasks that point to the queue.
It seems like I really need something like this (which doesn't exist):
Queue.objects.annotate(num_tasks=Count('task__status=Task.CLOSED_STATUS'))
At a higher level, I am trying to find a way to sort my queues based
on the number of tasks that point to the queue of a particular status.
IE, the user would be able to sort their queues based on number of
open tasks or number of closed tasks. Perhaps there is some other
approach that I am missing ...
Margie
On Mar 15, 2:43 am, Tom Evans wrote:
> On Mon, Mar 14, 2011 at 8:57 PM,MargieRoginski
>
> wrote:
> > class Queue(models.Model):
> > # fields here, not relevant for this discussion
>
> > class Task(models.Mode):
> > queue = models.ForeignKey("Queue")
> > status = models.IntegerField(choices=STATUS_CHOICES)
>
> > I am trying to create a Queue queryset that willannotateeach Queue
> > with the number of tasks whose queue field is pointing to that Queue
> > and status field has a certain value.
>
> > I don't thinkannotatewill work for me due to me needing to count up
> > only tasks whose status field has a certain value. I think I might
> > need extra? But I'm having touble making that work.
>
> No, this is precisely whatannotateis for.
>
> Queue.objects.filter(task__status=Task.SOME_CHOICE).annotate(num_tasks=Count('task'))
>
> Cheers
>
> Tom
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: How to use extra to count based on the value of a field?
Ah, right - so obvious! I'm not sure why it didn't occur to me to
filter first, then annotate. Thank you!
On Mar 15, 2:43 am, Tom Evans wrote:
> On Mon, Mar 14, 2011 at 8:57 PM, Margie Roginski
>
> wrote:
> > class Queue(models.Model):
> > # fields here, not relevant for this discussion
>
> > class Task(models.Mode):
> > queue = models.ForeignKey("Queue")
> > status = models.IntegerField(choices=STATUS_CHOICES)
>
> > I am trying to create a Queue queryset that will annotate each Queue
> > with the number of tasks whose queue field is pointing to that Queue
> > and status field has a certain value.
>
> > I don't think annotate will work for me due to me needing to count up
> > only tasks whose status field has a certain value. I think I might
> > need extra? But I'm having touble making that work.
>
> No, this is precisely what annotate is for.
>
> Queue.objects.filter(task__status=Task.SOME_CHOICE).annotate(num_tasks=Count('task'))
>
> Cheers
>
> Tom
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
Re: How to use extra to count based on the value of a field?
On Mon, Mar 14, 2011 at 8:57 PM, Margie Roginski
wrote:
> class Queue(models.Model):
> # fields here, not relevant for this discussion
>
> class Task(models.Mode):
> queue = models.ForeignKey("Queue")
> status = models.IntegerField(choices=STATUS_CHOICES)
>
> I am trying to create a Queue queryset that will annotate each Queue
> with the number of tasks whose queue field is pointing to that Queue
> and status field has a certain value.
>
> I don't think annotate will work for me due to me needing to count up
> only tasks whose status field has a certain value. I think I might
> need extra? But I'm having touble making that work.
No, this is precisely what annotate is for.
Queue.objects.filter(task__status=Task.SOME_CHOICE).annotate(num_tasks=Count('task'))
Cheers
Tom
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
How to use extra to count based on the value of a field?
class Queue(models.Model):
# fields here, not relevant for this discussion
class Task(models.Mode):
queue = models.ForeignKey("Queue")
status = models.IntegerField(choices=STATUS_CHOICES)
I am trying to create a Queue queryset that will annotate each Queue
with the number of tasks whose queue field is pointing to that Queue
and status field has a certain value.
I don't think annotate will work for me due to me needing to count up
only tasks whose status field has a certain value. I think I might
need extra? But I'm having touble making that work.
qs = Queue.objects.extra(select={'task_open_count':"select count(*)
from taskmanager_task inner join taskmanager_queue on
taskmanager_task.queue_id = taskmanager_queue.id where
taskmanager_task.status=1"})
I know this isn't doing the right thing. This seems to be annotating
all of the resulting queues withthe total number of open tasks (I
think).
Can anyone give me a hand? Thank you!
Margie
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
