Opened 15 years ago

Closed 15 years ago

#9997 closed (fixed)

Nested queries support: Field for ValuesListQuerySet (flat=True) gets lost in query construction

Reported by: Matthias Kestenholz Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: dev
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have a model called Favorite which has a generic foreign key. I want to get all bookmarked jobs for a user efficiently, therefore I query the database for all object_ids:

ids = Favorite.objects.get_favorites_by_model(Job).filter(user=1).values_list('object_id', flat=True)

In [28]: ids
Out[28]: [338L, 308L, 212L, 345L, 307L, 264L, 322L, 354L, 326L, 328L, 335L, 334L, 332L, 353L, 352L, 351L, 350L, 349L, 316L]

In [33]: type(ids)
Out[33]: <class 'django.db.models.query.ValuesListQuerySet'>

Now, using this information I want to get a list of all jobs. However, the following two statements yield completely different results. Only the former is correct:

In [22]: Job.objects.filter(id__in=tuple(ids))
Out[22]: [<Job....

In [20]: Job.objects.filter(id__in=ids)
Out[20]: [<Job....

The two corresponding SQL statements are:

{'sql': u'SELECT jobs_job.id, jobs_job.created, jobs_job.modified, jobs_job.created_by_id, jobs_job.name, jobs_job.notes, jobs_job.job_id, jobs_job.customer_id, jobs_job.contact_id, jobs_job.project_id, jobs_job.state, jobs_job.manager_id, jobs_job.no_profit, jobs_job.prepayment, jobs_job.archived_on FROM jobs_job WHERE jobs_job.id IN (338, 308, 212, 345, 307, 264, 322, 354, 326, 328, 335, 334, 332, 353, 352, 351, 350, 349, 316) ORDER BY jobs_job.job_id DESC LIMIT 21',

'time': '0.001'}

{'sql': u'SELECT jobs_job.id, jobs_job.created, jobs_job.modified, jobs_job.created_by_id, jobs_job.name, jobs_job.notes, jobs_job.job_id, jobs_job.customer_id, jobs_job.contact_id, jobs_job.project_id, jobs_job.state, jobs_job.manager_id, jobs_job.no_profit, jobs_job.prepayment, jobs_job.archived_on FROM jobs_job WHERE jobs_job.id IN (SELECT U0.id FROM favorites_favorite U0 WHERE (U0.content_type_id = 20 AND U0.user_id = 1 )) ORDER BY jobs_job.job_id DESC LIMIT 21',

'time': '0.001'}

Note how the subquery selects the primary key instead of the object_id.

Change History (4)

comment:1 by Matthias Kestenholz, 15 years ago

Component: UncategorizedDatabase layer (models, ORM)

comment:2 by Matthias Kestenholz, 15 years ago

Version: 1.0SVN

(sorry for the spam, will try to correctly set the ticket properties from the start next time)

comment:3 by Malcolm Tredinnick, 15 years ago

Owner: changed from nobody to Malcolm Tredinnick
Status: newassigned

Hmm. Good point. This opens a whole other bag of worms for how people are going to use and abuse nested queries, too. Time to insert a bunch of error messages along with some fixes.

comment:4 by Malcolm Tredinnick, 15 years ago

Resolution: fixed
Status: assignedclosed

(In [9759]) Fixed #9997 -- Fixed use of ValuesQuerySets as rvalues in filters.

Previous behaviour was pretty stupid. Let's never speak of it again. New
behaviour both works and is documented.

Note: See TracTickets for help on using tickets.
Back to Top