#10723: ORM exclute strange
---------------------------------------------------+------------------------
Reporter: anonymous | Owner: nobody
Status: new | Milestone: 1.1
Component: Database layer (models, ORM) | Version: SVN
Resolution: | Keywords:
Stage: Accepted | Has_patch: 0
Needs_docs: 0 | Needs_tests: 0
Needs_better_patch: 0 |
---------------------------------------------------+------------------------
Changes (by mtredinnick):
* needs_better_patch: => 0
* needs_tests: => 0
* milestone: => 1.1
* needs_docs: => 0
* stage: Unreviewed => Accepted
Old description:
> My models is:
> {{{
> class Type(models.Model):
> id = models.AutoField(primary_key=True)
> slug = models.SlugField(unique=True, max_length=10)
> title = models.CharField(max_length=20)
> weight = models.SmallIntegerField(default=0, db_index=True)
>
> class Active(models.Model):
> post = models.ForeignKey(Post, related_name='activeposts')
> rubrica = models.ForeignKey(Rubrica, related_name='activeposts')
> type = models.ForeignKey(Type, related_name='activeposts')
> user_id = models.PositiveIntegerField()
> pub_date = models.DateTimeField(default=datetime.now)
> }}}
>
> {{{
> Type.objects.filter(Q(activeposts__rubrica__pk=112) &
> Q(activeposts__user_id__in=[1,2,3])).annotate(count=Count('activeposts')).query.as_sql()
> }}}
>
> Result is correct:
>
> {{{
> ('SELECT `doska_type`.`id`, `doska_type`.`slug`, `doska_type`.`weight`,
> COUNT(`doska_active_new`.`id`) AS `count` FROM `doska_type` LEFT OUTER
> JOIN `doska_active_new` ON (`doska_type`.`id` =
> `doska_active_new`.`type_id`) WHERE (`doska_active_new`.`rubrica_id` = %s
> AND `doska_active_new`.`user_id` IN (%s, %s, %s)) GROUP BY
> `doska_type`.`id` ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3))
> }}}
> But
> {{{
> Type.objects.filter(Q(activeposts__rubrica__pk=112) &
> ~Q(activeposts__user_id__in=[1,2,3])).annotate(count=Count('activeposts')).query.as_sql()
> }}}
>
> Result is not correct:
>
> {{{
> ('SELECT `doska_type`.`id`, `doska_type`.`slug`, `doska_type`.`weight`,
> COUNT(`doska_active_new`.`id`) AS `count` FROM `doska_type` LEFT OUTER
> JOIN `doska_active_new` ON (`doska_type`.`id` =
> `doska_active_new`.`type_id`) WHERE (`doska_active_new`.`rubrica_id` = %s
> AND NOT (`doska_type`.`id` IN (SELECT U1.`type_id` FROM
> `doska_active_new` U1 WHERE U1.`user_id` IN (%s, %s, %s)))) GROUP BY
> `doska_type`.`id` ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3))
> }}}
New description:
My models is:
{{{
#!python
class Type(models.Model):
id = models.AutoField(primary_key=True)
slug = models.SlugField(unique=True, max_length=10)
title = models.CharField(max_length=20)
weight = models.SmallIntegerField(default=0, db_index=True)
class Active(models.Model):
post = models.ForeignKey(Post, related_name='activeposts')
rubrica = models.ForeignKey(Rubrica, related_name='activeposts')
type = models.ForeignKey(Type, related_name='activeposts')
user_id = models.PositiveIntegerField()
pub_date = models.DateTimeField(default=datetime.now)
}}}
{{{
#!python
Type.objects.filter(Q(activeposts__rubrica__pk=112) & \
Q(activeposts__user_id__in= 1,2,3])). \
annotate(count=Count('activeposts')).query.as_sql()
}}}
Result is correct:
{{{
#!sql
('SELECT `doska_type`.`id`, `doska_type`.`slug`,
`doska_type`.`weight`, COUNT(`doska_active_new`.`id`) AS `count`
FROM `doska_type` LEFT OUTER JOIN `doska_active_new`
ON (`doska_type`.`id` = `doska_active_new`.`type_id`)
WHERE (`doska_active_new`.`rubrica_id` = %s
AND `doska_active_new`.`user_id` IN (%s, %s, %s))
GROUP BY `doska_type`.`id`
ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3))
}}}
But
{{{
#!python
Type.objects.filter(Q(activeposts__rubrica__pk=112) & \
~Q(activeposts__user_id__in=[1,2,3])). \
annotate(count=Count('activeposts')).query.as_sql()
}}}
Result is not correct:
{{{
#!sql
('SELECT `doska_type`.`id`, `doska_type`.`slug`,
`doska_type`.`weight`, COUNT(`doska_active_new`.`id`) AS `count`
FROM `doska_type` LEFT OUTER JOIN `doska_active_new`
ON (`doska_type`.`id` = `doska_active_new`.`type_id`)
WHERE (`doska_active_new`.`rubrica_id` = %s
AND NOT (`doska_type`.`id` IN (
SELECT U1.`type_id`
FROM `doska_active_new` U1
WHERE U1.`user_id` IN (%s, %s, %s))))
GROUP BY `doska_type`.`id`
ORDER BY `doska_type`.`weight` ASC', (112, 1, 2, 3))
}}}
Comment:
(Reformatted description to avoid lots of side-scrolling.)
--
Ticket URL: <http://code.djangoproject.com/ticket/10723#comment:1>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en
-~----------~----~----~----~------~----~------~--~---
