Re: DB queries with filter and exclude

[Nis Jørgensen]

[EMAIL PROTECTED] skrev:
> Wow, thanks for the reply. I can't get this to work though--I get an
> "iteration over non-sequence". Can you take a look?
>   
author.book_set is a Manager. You need author.book_set.all()  (or
author.book_set.all in a template) to get a QuerySet which you can
iterate over.

Nis



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: DB queries with filter and exclude

[[EMAIL PROTECTED]]

Wow, thanks for the reply. I can't get this to work though--I get an
"iteration over non-sequence". Can you take a look?

model...

class Author(models.Model):
first_name = models.CharField(maxlength=30)
last_name = models.CharField(maxlength=40)

def __str__(self):
return '%s %s' % (self.first_name, self.last_name)

class Book(models.Model):
title = models.CharField(maxlength=100)
authors = models.ManyToManyField(Author)

def __str__(self):
return self.title

view...

def authors(request):
a = Author.objects.all()
return render_to_response('books/authors.html', {'authors':a,})

template...


{% for author in authors %}
{{ author }}


{% for book in author.book_set %}
{{ book }}{% if forloop.last %}{% else %}, {% endif %}
{% endfor %}



{% endfor %}



--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: DB queries with filter and exclude

[[EMAIL PROTECTED]]

Given your view code above, what would the template code look like to
return something like this:

object | tags
---+--
  obj1  | food, meat
  obj2  | food, meat
  obj3  | food
  obj4  | food, cake
  obj5  | food, cake
  obj6  | food, vegetable
  obj7  | garden, vegetable



On Aug 6, 1:46 pm, Tim Chase <[EMAIL PROTECTED]> wrote:
> > Now I want to get all objects, which have set tag "food" *and* *have
> > not* set tag "cake".
>
> > This get all objects with tag="food" set (obj1..obj5):
>
> >Obj.objects.filter(tags__in=[Tags.objects.filter(name='food')])
>
> > This get all objects, which haven't set tag "cake" (obj1..obj3, obj6..obj7):
>
> >Obj.objects.exclude(tags__in=[Tags.objects.filter(name='cake')])
>
> > When I try to combine DB queries together, I don't get what I expect
> > (obj1..obj3, obj6):
>
> Django's ORM doesn't handle this sort of thing very gracefully,
> so it requires dropping down to a little SQL with .extra() calls.
>
> Something like
>
>   Obj.objects.extra(where=["""
>  app_model.id IN (
>SELECT model_id
>FROM app_tags
>WHERE name = ?
>  )
>  """, ['food']).extra(where["""
>  app_model.id NOT IN (
>SELECT model_id
>FROM app_tags
>WHERE name = ?
>  )
>  """, ['cake'])
>
> it's ugly and hackish, but when things scale up, this is the only
> way to get it to work (I've got several hundred thousand records
> in my app that does logic like this, so I need all the
> speed-boost I can get)
>
> if your tagsets return small collections, you could use
> sets...something like
>
>   food = set(t.obj_id for t in Tags.objects.filter(name='food'))
>   cake = set(t.obj_id for t in Tags.objects.filter(name='cake'))
>   Obj.objects.filter(id__in=food - cake)
>
> (that last line might need to be "tuple(food-cake)")
>
> but as noted, this doesn't scale well once "food" or "cake" tags
> thousands of things (or maybe even hundreds of things).
>
> -tim


--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

Re: DB queries with filter and exclude

[Tim Chase]

> Now I want to get all objects, which have set tag "food" *and* *have 
> not* set tag "cake".
> 
> This get all objects with tag="food" set (obj1..obj5):
> 
>Obj.objects.filter(tags__in=[Tags.objects.filter(name='food')])
> 
> This get all objects, which haven't set tag "cake" (obj1..obj3, obj6..obj7):
> 
>Obj.objects.exclude(tags__in=[Tags.objects.filter(name='cake')])
> 
> When I try to combine DB queries together, I don't get what I expect 
> (obj1..obj3, obj6):


Django's ORM doesn't handle this sort of thing very gracefully, 
so it requires dropping down to a little SQL with .extra() calls.

Something like

  Obj.objects.extra(where=["""
 app_model.id IN (
   SELECT model_id
   FROM app_tags
   WHERE name = ?
 )
 """, ['food']).extra(where["""
 app_model.id NOT IN (
   SELECT model_id
   FROM app_tags
   WHERE name = ?
 )
 """, ['cake'])

it's ugly and hackish, but when things scale up, this is the only 
way to get it to work (I've got several hundred thousand records 
in my app that does logic like this, so I need all the 
speed-boost I can get)

if your tagsets return small collections, you could use 
sets...something like

  food = set(t.obj_id for t in Tags.objects.filter(name='food'))
  cake = set(t.obj_id for t in Tags.objects.filter(name='cake'))
  Obj.objects.filter(id__in=food - cake)

(that last line might need to be "tuple(food-cake)")

but as noted, this doesn't scale well once "food" or "cake" tags 
thousands of things (or maybe even hundreds of things).

-tim





--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---

DB queries with filter and exclude

[Michal]

Hello,
I have defined two models, one reffered from another with ManyToManyField.

This referrer is used to categorising objects with 0, 1 or more tags. 
Now I want to get all objects, which have set tag "food" *and* *have 
not* set tag "cake".

Example:

object | tags
---+--
  obj1  | food, meat
  obj2  | food, meat
  obj3  | food
  obj4  | food, cake
  obj5  | food, cake
  obj6  | food, vegetable
  obj7  | garden, vegetable

This get all objects with tag="food" set (obj1..obj5):

   Obj.objects.filter(tags__in=[Tags.objects.filter(name='food')])

This get all objects, which haven't set tag "cake" (obj1..obj3, obj6..obj7):

   Obj.objects.exclude(tags__in=[Tags.objects.filter(name='cake')])

When I try to combine DB queries together, I don't get what I expect 
(obj1..obj3, obj6):

 
Obj.objects.filter(tags__in=[Tags.objects.filter(name='food')]).objects.exclude(tags__in=[Tags.objects.filter(name='cake')])

How to construct queries like this please?


Regards
Michal

--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en
-~--~~~~--~~--~--~---