On Thu, 2009-04-16 at 18:20 -0700, Thierry wrote: > Let's say I have a list of words in my database: > > ['bbb', 'aaa', 'zzz', 'ddd'] > > How can I retrieve a list of the above excluding the following words > ['aaa', 'zzz'] by using __in? I can do the above with: > > words_list = Words.objects.exclude( > Q(word_name = 'aaa') | Q(word_name = 'zzz') > ) > > The following type of syntax doesn't seem to work with exclude but > works with filter: > > exclude_list = ['aaa', 'zzz'] > country _list = Countries.objects.exclude(word_name__in = > exclude_list)
How are your models constructed? In particular, is word_name a simple CharField (or similar direct value) on the Countries model, or something related to the Word model? Your first query was talking about the Word object, this one is talking about the Countries object. The exclude() query that you've written will work correctly, in that it will return all Countries objects that do not have a word_name of anything in the exclude_list. That isn't the same as the first queryset you constructed, which will return all Word objects which contain a word_name which does not match "aaa" or "zzz". The difference is when word_name is a ManyToMany field (or other multi-valued situation), when the first queryset would return a Word object that contained to word_name values providing one of them was not "aaa" or one of them was not "zzz". If word_name is something like a CharField, the two querysets you've constructed will produce almost exactly the same SQL (in fact, but the time the database server's optimiser is done working with them, they probably will be *exactly* the same query plan, since IN queries are often turned into a series of disjunctions as part of the optimisation path inside the server). So I'm not sure what you mean when you say the second version doesn't work. What are you expecting to happen and what actually happened? Can you explain that using only a single version of the model, instead of Word the first time and Countries the second time? > > The other operation I want to do is to construct a list where I hand > pick two items and place it at the beginning of a list while the rest > is sorted. For example, if I want 'zzz' and 'ddd' to be at the > beginning of the list while the rest is sorted, my desired output will > look like: > > ['zzz', 'ddd', 'aaa', 'bbb'] Repeat after me: "I shall not attempt to abuse SQL in this fashion. It is not the shovel I am looking for to hammer in these screws." SQL is about sets (it's a relational algebra implementation). The weak-willed pussies who wrote the language spec caved in and also permitted ordering to be applied as one of the last operations in constructing a result, ruining the "set" part slightly, but that's as far as it goes. You only get one pass at ordering in a select statement. So you could construct some kind of complicated SQL that attached an annotation to each row indicating that "zzz" should be higher priority than "ddd" and then everything else and then order by that annotation value. That will require using SQL directly, however, since it's out of scope for Django (and Django isn't intended to ever entirely replace SQL, since SQL is a perfectly good language for talking to databases). > I could do the above in 2 steps but I end up with two QuerySet objects > which I cannot join together. Any help on the above? Why not just pull everything back as a single queryset, then convert it to a Python list and reorder it as you wish. Querysets are primarily useful as iterators by the stage that you're wanting to do arbitrary manipulations with them, so converting it to a similar, but more useful for your case, iterator -- the Python list, which allows updating as well as just reading -- is a fairly justifiable solution. Regards, Malcolm --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---