On Thu, 2009-04-16 at 23:11 -0700, Tai Lee wrote: > I'm trying to add an extra selection to a queryset, `featured`, which > should be True (or 1) if the primary key matches a number of known > values. > > It appears to work as expected both in SQL and the ORM when matching > against a single value. When I try matching against multiple values, I > get the expected results in SQL (`featured` is set to `1` for matching > rows), but in the ORM all rows set `featured` to 0. > > I've used the User model in an example, below. There's also a pretty > version at http://dpaste.com/hold/34599/ > > # interactive shell. > > >>> from django.contrib.auth.models import User > > # the specified user (1) is returned with `featured` as 1. > > >>> q = User.objects.extra( > ... select={'featured': 'auth_user.id IN (%s)'}, > ... select_params=['1'] > ... ).values('featured', 'pk', 'username').order_by('-featured', 'pk', > 'username') > >>> print q.query > SELECT (auth_user.id IN (1)) AS "featured", "auth_user"."id", > "auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC, > "auth_user"."id" ASC, "auth_user"."username" ASC > >>> print list(q) > [{'username': u'admin', 'pk': 1, 'featured': 1}, {'username': > u'manager', 'pk': 2, 'featured': 0}] > > # both of the specified users (1,2) are both incorrectly returned with > `featured` as 0. > > >>> q = User.objects.extra( > ... select={'featured': 'auth_user.id IN (%s)'}, > ... select_params=['1,2'] > ... ).values('featured', 'pk', 'username').order_by('-featured', 'pk', > 'username') > >>> print q.query > SELECT (auth_user.id IN (1,2)) AS "featured", "auth_user"."id", > "auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC, > "auth_user"."id" ASC, "auth_user"."username" ASC > >>> print list(q) > [{'username': u'admin', 'pk': 1, 'featured': 0}, {'username': > u'manager', 'pk': 2, 'featured': 0}] > > # the sql statements generated for both querysets are valid and work > as expected when executed from the sqlite shell directly. > > sqlite> SELECT (auth_user.id IN (1)) AS "featured", "auth_user"."id", > "auth_user"."username" FROM "auth_user" ORDER BY "featured" DESC, > "auth_user"."id" ASC, "auth_user"."username" ASC > 1|1|admin > 0|2|manager > > sqlite> SELECT (auth_user.id IN (1,2)) AS "featured", > "auth_user"."id", "auth_user"."username" FROM "auth_user" ORDER BY > "featured" DESC, "auth_user"."id" ASC, "auth_user"."username" ASC > 1|1|admin > 1|2|manager > sqlite>
The more relevant test here is what happens when you remove only Django from the equation and use pysqlite or the sqlite Python module to do the query. It could be something happening at that level. Off the top of my head, I can't think of anything Django's doing here that would be changing things. Realise, too, that pysqlite/sqlite module version, sqlite binary version and Django version are all likely to be relevant variables here (although less so for the Django version). SQLite changes fairly rapidly in terms of fixing bugs and its weak-typing behaviour does lead to strange things like this from time to time. 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 -~----------~----~----~----~------~----~------~--~---
