Re: Negate querysets

[Vinicius Mendes | meiocodigo.com]

Ok. I thought it was more simple. I don't know the ORM in deep and
forgot about the extra() method. Thanks for the explanation.

On Mar 23, 5:15 pm, James Bennett  wrote:
> On Tue, Mar 23, 2010 at 10:21 AM, Vinicius Mendes | meiocodigo.com
>
>  wrote:
> > integrated to the framework. I think the queryset should keep track of
> > it self. It knows what is the filter, so why can't it negate this
> > filter?
>
> Given an already-existing QuerySet which has already had all the
> filters applied, there's really no way to do this. The reason is that
> there's so much more that can be done to a QuerySet than just calls to
> filter(); for example, calls to extra() may have added additional raw
> SQL in the SELECT or WHERE clauses, and the ORM doesn't have any way
> to work out how to negate arbitrary user-supplied SQL.
>
> The WHERE clause itself is represented on the Query object by an
> instance of django.db.models.sql.where.WhereNode, which is actually a
> tree structure with each node representing part of the WHERE clause.
> Each node in that tree does know whether it's negated or not (and
> hence whether to use a NOT on the constraint each node expresses), but
> walking that tree and figuring out which nodes you'd want to negate
> would be difficult to say the least, and still wouldn't work on a
> QuerySet which had been affected by something like extra().
>
> So about the only way to do this is to collect the filter arguments in
> advance *without* applying them to the QuerySet, and then use either
> exclude() or negated Q objects to ensure you get a "negated" query.
>
> --
> "Bureaucrat Conrad, you are technically correct -- the best kind of correct."

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[James Bennett]

On Tue, Mar 23, 2010 at 10:21 AM, Vinicius Mendes | meiocodigo.com
 wrote:
> integrated to the framework. I think the queryset should keep track of
> it self. It knows what is the filter, so why can't it negate this
> filter?

Given an already-existing QuerySet which has already had all the
filters applied, there's really no way to do this. The reason is that
there's so much more that can be done to a QuerySet than just calls to
filter(); for example, calls to extra() may have added additional raw
SQL in the SELECT or WHERE clauses, and the ORM doesn't have any way
to work out how to negate arbitrary user-supplied SQL.

The WHERE clause itself is represented on the Query object by an
instance of django.db.models.sql.where.WhereNode, which is actually a
tree structure with each node representing part of the WHERE clause.
Each node in that tree does know whether it's negated or not (and
hence whether to use a NOT on the constraint each node expresses), but
walking that tree and figuring out which nodes you'd want to negate
would be difficult to say the least, and still wouldn't work on a
QuerySet which had been affected by something like extra().

So about the only way to do this is to collect the filter arguments in
advance *without* applying them to the QuerySet, and then use either
exclude() or negated Q objects to ensure you get a "negated" query.


-- 
"Bureaucrat Conrad, you are technically correct -- the best kind of correct."

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Phlip]

> I want to be able to answer your question, forever. Is there a way,
> from a developer test, to query "what SQL statement does this QuerySet
> generate"?

Oh, duh, it's QuerySet.query, as a string.

Brand X makes that one inconceivably hard, due to poor factoring...

> --
>   Phlip
>  http://c2.com/cgi/wiki?ZeekLand

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Phlip]

Matt Schinckel wrote:

> Are you sure it hits the db twice?

Of course not. But (modulo "Premature Optimization") I would be
suspicious of any such statement, as I wrote it.

I want to be able to answer your question, forever. Is there a way,
from a developer test, to query "what SQL statement does this QuerySet
generate"?

A related question is "what are all the SQL statements that a given
block of code create?"

If I knew the answer to those questions, I could write an assertion
that sends the output into EXPLAIN, and then detects atrocities, such
as table reads. This would allow developer tests to fail as early as
possible, without soak testing to detect the slow parts by brute
force. And such an assertion would permit aggressive refactoring,
without worrying that one refactor will silently slow everything down.

Oh, also, a unified statement like "SELECT * WHERE id not in (SELECT
id WHERE ...)" might just snocker the database worse than two SELECTs.
You never know; that's why I'd like to see an EXPLAIN on it!

--
  Phlip
  http://c2.com/cgi/wiki?ZeekLand

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Vinicius Mendes | meiocodigo.com]

Matt,

I know I can do this, but if I want to write a generic view that
receives a queryset and deletes everything that isn't in this
queryset? I'm not having this problem right now. I just thought that
this was a feature that would be interesting to have in the ORM. And I
am asking if it already exists to think of suggesting it to be
integrated to the framework. I think the queryset should keep track of
it self. It knows what is the filter, so why can't it negate this
filter?

On Mar 23, 10:40 am, Paulo Almeida 
wrote:
> Is there some way you can keep track of the queryset? I understand it's
> dynamic, and maybe complex, for you to be looking for this kind of solution,
> but if it were possible to have a variable or dictionary keeping track of
> what you add to the queryset, it might be possible to use it to build the
> inverse.
>
> - Paulo
>
> On Tue, Mar 23, 2010 at 1:23 PM, Vinicius Mendes | meiocodigo.com <
>
>
>
> vbmen...@gmail.com> wrote:
> > Ok. The code proposed by Tim Shaffer works and gives only one query.
> > But it makes use of subselects, what is heavy for the database. Take a
> > look at the generated SQL:
>
> > 'SELECT `auth_user`.`id`, `auth_user`.`username`,
> > `auth_user`.`first_name`, `auth_user`.`last_name`,
> > `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
> > `auth_user`.`is_active`, `auth_user`.`is_superuser`,
> > `auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
> > WHERE NOT (`auth_user`.`id` IN (SELECT U0.`id` FROM `auth_user` U0
> > WHERE U0.`first_name` = vinicius )) LIMIT 21'
>
> > I was thinking of something like:
>
> > 'SELECT `auth_user`.`id`, `auth_user`.`username`,
> > `auth_user`.`first_name`, `auth_user`.`last_name`,
> > `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
> > `auth_user`.`is_active`, `auth_user`.`is_superuser`,
> > `auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
> > WHERE NOT `auth_user`.`first_name` = vinicius  LIMIT 21'
>
> > Why hit the database if I can do this with boolean login? Why not
> > negate the filter condition instead of doing a subselect?
>
> > On Mar 22, 7:15 pm, Tim Shaffer  wrote:
> > > It depends. This will only run one query, when negated_queryset is
> > > used.
>
> > > queryset = User.objects.filter(first_name='vinicius')
> > > negated_queryset = User.objects.exclude(id__in=queryset.values("id"))
>
> > > Since the first queryset is not evaluated until it's used in the
> > > negated_queryset (as a subquery).
>
> > > On Mar 22, 5:55 pm, Matt Schinckel  wrote:
>
> > > > On Mar 23, 6:17 am, Phlip  wrote:
>
> > > > > > Just create another queryset that excludes everything in your first
> > > > > > queryset:
>
> > > > > > negated_queryset =
> > User.objects.exclude(id__in=queryset.values("id"))
>
> > > > > QuerySets are already so easy to plug-n-play... Ain't there a way to
> > > > > do it without whacking the database twice?
>
> > > > Are you sure it hits the db twice? I seem to recall a similar case
> > > > where I thought I was, but since the queryset evaluation is lazy, the
> > > > ORM potentially has the ability to make this into a single query.
>
> > > > (I can't recall if when I did this sort of thing I was using
> > > > SQLAlchemy, but I _think_ it was pure django).
>
> > > > Matt.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "Django users" group.
> > To post to this group, send email to django-us...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > django-users+unsubscr...@googlegroups.com > groups.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-us...@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: Negate querysets

[Paulo Almeida]

Is there some way you can keep track of the queryset? I understand it's
dynamic, and maybe complex, for you to be looking for this kind of solution,
but if it were possible to have a variable or dictionary keeping track of
what you add to the queryset, it might be possible to use it to build the
inverse.

- Paulo

On Tue, Mar 23, 2010 at 1:23 PM, Vinicius Mendes | meiocodigo.com <
vbmen...@gmail.com> wrote:

> Ok. The code proposed by Tim Shaffer works and gives only one query.
> But it makes use of subselects, what is heavy for the database. Take a
> look at the generated SQL:
>
> 'SELECT `auth_user`.`id`, `auth_user`.`username`,
> `auth_user`.`first_name`, `auth_user`.`last_name`,
> `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
> `auth_user`.`is_active`, `auth_user`.`is_superuser`,
> `auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
> WHERE NOT (`auth_user`.`id` IN (SELECT U0.`id` FROM `auth_user` U0
> WHERE U0.`first_name` = vinicius )) LIMIT 21'
>
> I was thinking of something like:
>
> 'SELECT `auth_user`.`id`, `auth_user`.`username`,
> `auth_user`.`first_name`, `auth_user`.`last_name`,
> `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
> `auth_user`.`is_active`, `auth_user`.`is_superuser`,
> `auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
> WHERE NOT `auth_user`.`first_name` = vinicius  LIMIT 21'
>
> Why hit the database if I can do this with boolean login? Why not
> negate the filter condition instead of doing a subselect?
>
> On Mar 22, 7:15 pm, Tim Shaffer  wrote:
> > It depends. This will only run one query, when negated_queryset is
> > used.
> >
> > queryset = User.objects.filter(first_name='vinicius')
> > negated_queryset = User.objects.exclude(id__in=queryset.values("id"))
> >
> > Since the first queryset is not evaluated until it's used in the
> > negated_queryset (as a subquery).
> >
> > On Mar 22, 5:55 pm, Matt Schinckel  wrote:
> >
> >
> >
> > > On Mar 23, 6:17 am, Phlip  wrote:
> >
> > > > > Just create another queryset that excludes everything in your first
> > > > > queryset:
> >
> > > > > negated_queryset =
> User.objects.exclude(id__in=queryset.values("id"))
> >
> > > > QuerySets are already so easy to plug-n-play... Ain't there a way to
> > > > do it without whacking the database twice?
> >
> > > Are you sure it hits the db twice? I seem to recall a similar case
> > > where I thought I was, but since the queryset evaluation is lazy, the
> > > ORM potentially has the ability to make this into a single query.
> >
> > > (I can't recall if when I did this sort of thing I was using
> > > SQLAlchemy, but I _think_ it was pure django).
> >
> > > Matt.
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-us...@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-us...@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: Negate querysets

[Matt Schinckel]

On Mar 23, 11:23 pm, "Vinicius Mendes | meiocodigo.com"
 wrote:
> Ok. The code proposed by Tim Shaffer works and gives only one query.
> But it makes use of subselects, what is heavy for the database. Take a
> look at the generated SQL:
>
> 'SELECT `auth_user`.`id`, `auth_user`.`username`,
> `auth_user`.`first_name`, `auth_user`.`last_name`,
> `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
> `auth_user`.`is_active`, `auth_user`.`is_superuser`,
> `auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
> WHERE NOT (`auth_user`.`id` IN (SELECT U0.`id` FROM `auth_user` U0
> WHERE U0.`first_name` = vinicius )) LIMIT 21'
>
> I was thinking of something like:
>
> 'SELECT `auth_user`.`id`, `auth_user`.`username`,
> `auth_user`.`first_name`, `auth_user`.`last_name`,
> `auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
> `auth_user`.`is_active`, `auth_user`.`is_superuser`,
> `auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
> WHERE NOT `auth_user`.`first_name` = vinicius  LIMIT 21'

For this example, you can just use:

>>> User.objects.exclude(first_name='vinicius')

Matt.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Vinicius Mendes | meiocodigo.com]

Ok. The code proposed by Tim Shaffer works and gives only one query.
But it makes use of subselects, what is heavy for the database. Take a
look at the generated SQL:

'SELECT `auth_user`.`id`, `auth_user`.`username`,
`auth_user`.`first_name`, `auth_user`.`last_name`,
`auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
`auth_user`.`is_active`, `auth_user`.`is_superuser`,
`auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
WHERE NOT (`auth_user`.`id` IN (SELECT U0.`id` FROM `auth_user` U0
WHERE U0.`first_name` = vinicius )) LIMIT 21'

I was thinking of something like:

'SELECT `auth_user`.`id`, `auth_user`.`username`,
`auth_user`.`first_name`, `auth_user`.`last_name`,
`auth_user`.`email`, `auth_user`.`password`, `auth_user`.`is_staff`,
`auth_user`.`is_active`, `auth_user`.`is_superuser`,
`auth_user`.`last_login`, `auth_user`.`date_joined` FROM `auth_user`
WHERE NOT `auth_user`.`first_name` = vinicius  LIMIT 21'

Why hit the database if I can do this with boolean login? Why not
negate the filter condition instead of doing a subselect?

On Mar 22, 7:15 pm, Tim Shaffer  wrote:
> It depends. This will only run one query, when negated_queryset is
> used.
>
> queryset = User.objects.filter(first_name='vinicius')
> negated_queryset = User.objects.exclude(id__in=queryset.values("id"))
>
> Since the first queryset is not evaluated until it's used in the
> negated_queryset (as a subquery).
>
> On Mar 22, 5:55 pm, Matt Schinckel  wrote:
>
>
>
> > On Mar 23, 6:17 am, Phlip  wrote:
>
> > > > Just create another queryset that excludes everything in your first
> > > > queryset:
>
> > > > negated_queryset = User.objects.exclude(id__in=queryset.values("id"))
>
> > > QuerySets are already so easy to plug-n-play... Ain't there a way to
> > > do it without whacking the database twice?
>
> > Are you sure it hits the db twice? I seem to recall a similar case
> > where I thought I was, but since the queryset evaluation is lazy, the
> > ORM potentially has the ability to make this into a single query.
>
> > (I can't recall if when I did this sort of thing I was using
> > SQLAlchemy, but I _think_ it was pure django).
>
> > Matt.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Tim Shaffer]

It depends. This will only run one query, when negated_queryset is
used.

queryset = User.objects.filter(first_name='vinicius')
negated_queryset = User.objects.exclude(id__in=queryset.values("id"))

Since the first queryset is not evaluated until it's used in the
negated_queryset (as a subquery).

On Mar 22, 5:55 pm, Matt Schinckel  wrote:
> On Mar 23, 6:17 am, Phlip  wrote:
>
> > > Just create another queryset that excludes everything in your first
> > > queryset:
>
> > > negated_queryset = User.objects.exclude(id__in=queryset.values("id"))
>
> > QuerySets are already so easy to plug-n-play... Ain't there a way to
> > do it without whacking the database twice?
>
> Are you sure it hits the db twice? I seem to recall a similar case
> where I thought I was, but since the queryset evaluation is lazy, the
> ORM potentially has the ability to make this into a single query.
>
> (I can't recall if when I did this sort of thing I was using
> SQLAlchemy, but I _think_ it was pure django).
>
> Matt.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Matt Schinckel]

On Mar 23, 6:17 am, Phlip  wrote:
> > Just create another queryset that excludes everything in your first
> > queryset:
>
> > negated_queryset = User.objects.exclude(id__in=queryset.values("id"))
>
> QuerySets are already so easy to plug-n-play... Ain't there a way to
> do it without whacking the database twice?

Are you sure it hits the db twice? I seem to recall a similar case
where I thought I was, but since the queryset evaluation is lazy, the
ORM potentially has the ability to make this into a single query.

(I can't recall if when I did this sort of thing I was using
SQLAlchemy, but I _think_ it was pure django).

Matt.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Tim Shaffer]

Might be possible. I'm not terribly familiar with the innards of the
QuerySet class. Seems like it could get real complex real fast,
especially if you're using Q objects.

On Mar 22, 4:17 pm, Phlip  wrote:
> > Just create another queryset that excludes everything in your first
> > queryset:
>
> > negated_queryset = User.objects.exclude(id__in=queryset.values("id"))
>
> QuerySets are already so easy to plug-n-play... Ain't there a way to
> do it without whacking the database twice?
>
> --
>   Phlip
>  http://c2.com/cgi/wiki?ZeekLand

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Phlip]

> Just create another queryset that excludes everything in your first
> queryset:
>
> negated_queryset = User.objects.exclude(id__in=queryset.values("id"))

QuerySets are already so easy to plug-n-play... Ain't there a way to
do it without whacking the database twice?

--
  Phlip
  http://c2.com/cgi/wiki?ZeekLand

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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: Negate querysets

[Tim Shaffer]

Just create another queryset that excludes everything in your first
queryset:

negated_queryset = User.objects.exclude(id__in=queryset.values("id"))

On Mar 22, 3:47 pm, Vinicius Mendes  wrote:
> Is there any way to negate a queryset? Let's supose i have this queryset:
>
> User.objects.filter(first_name='vinicius')
>
> and I want to have the queryset with the objects that is not in first
> queryset. I know I can do this:
>
> User.objects.exclude(first_name='vinicius')
>
> But we have cases where we don't know what is the queryset, we only knows
> that we want to get the elements that isn't in it, like this:
>
> queryset.negate()
>
> It's good to keep the chainable property of querysets.
>
> __
> Vinícius Mendes
> Solucione Sistemashttp://solucione.info/

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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.

Negate querysets

[Vinicius Mendes]

Is there any way to negate a queryset? Let's supose i have this queryset:

User.objects.filter(first_name='vinicius')

and I want to have the queryset with the objects that is not in first
queryset. I know I can do this:

User.objects.exclude(first_name='vinicius')

But we have cases where we don't know what is the queryset, we only knows
that we want to get the elements that isn't in it, like this:

queryset.negate()

It's good to keep the chainable property of querysets.

__
Vinícius Mendes
Solucione Sistemas
http://solucione.info/

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-us...@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.