Re: annotation with filter issue (left join needed)

[Михаил Лукин]

It seems that you're right :( raw SQL takes away most of benefits of ORM

2009/11/10 akaariai 

>
>
>
> On Nov 10, 8:29 am, Михаил Лукин  wrote:
> > Well, I didn't find solution yet. Except that filter condition must be
> > placed in LEFT OUTER JOIN ... ON (  ), but i'm not sure if it's
> > possible with Django ORM. I notices that Aggregate base class takes
> 'extra'
> > argument in its constructor, but I'm not sure how to use it for such
> > purpose.
>
> Django can not handle left joins where the filter conditions need to
> be in the ON clause of the join. The comment for function join in
> django.db.models.sql  clearly states this.
>
> So if you really need to place the filter condition on the left outer
> join, then there is nothing you can do except use raw SQL.
>
> >
>


-- 
regards,
Mihail

--~--~-~--~~~---~--~~
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: annotation with filter issue (left join needed)

[akaariai]

On Nov 10, 8:29 am, Михаил Лукин  wrote:
> Well, I didn't find solution yet. Except that filter condition must be
> placed in LEFT OUTER JOIN ... ON (  ), but i'm not sure if it's
> possible with Django ORM. I notices that Aggregate base class takes 'extra'
> argument in its constructor, but I'm not sure how to use it for such
> purpose.

Django can not handle left joins where the filter conditions need to
be in the ON clause of the join. The comment for function join in
django.db.models.sql  clearly states this.

So if you really need to place the filter condition on the left outer
join, then there is nothing you can do except use raw SQL.

--~--~-~--~~~---~--~~
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: annotation with filter issue (left join needed)

[Михаил Лукин]

Well, I didn't find solution yet. Except that filter condition must be
placed in LEFT OUTER JOIN ... ON (  ), but i'm not sure if it's
possible with Django ORM. I notices that Aggregate base class takes 'extra'
argument in its constructor, but I'm not sure how to use it for such
purpose.

Any suggestions?

2009/11/6 Михаил Лукин 

> Hi Djangoers
>
> There is two models: task and report on this task.
>
> class Task(models.Model):
>   # some task fields here
>
> class Report(models.Model):
>   task = models.ForeignKey(Task)
>   time_spent = models.PositiveSmallIntegerField()
>   date_posted = models.DateField(auto_now_add=True)
>   # some report fields here
>
> I need to query time spent on every task within interval of time. Here is
> how I do it:
> Task.objects.all().annotate(time_spent=Sum('report__time_spent')).filter(report__date_posted__gte=start_date,
> report__date_posted__lte=due_date)
>
> Without filtering reports by date I can query general time spent on task
> even if there were no reports (it is None, and it's OK). But when using
> filter by date, when there is no reports posted on task, it is not returned
> by queryset. Is this LEFT JOIN issue or what? What can you suggest?
>
> Thanks
>
> --
> regards,
> Mihail
>
>


-- 
regards,
Mihail

--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---