Opened 15 years ago
Last modified 9 years ago
#11459 closed
QuerySet.count() generates invalid sql when used with select_related() and extra(where=[customWhereClause]) — at Initial Version
Reported by: | eddified | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.3 |
Severity: | Normal | Keywords: | QuerySet.extra |
Cc: | Jamey Sharp, j | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Here is the error message:
(1054, "Unknown column 'beyondStore_artist.name' in 'where clause'")
This is the invalid sql that calling count() is generating that causes the above error message:
SELECT COUNT(*) FROM sss_song
WHERE (match(sss_song.title) against('hits')) or (match(sss_artist.name, sss_artist.description) against('hits')) or (match(sss_album.title, sss_album.description) against('hits'))
Here is the code that generates the QuerySet:
songs = models.Song.objects.select_related('albumgenregenreid', 'artistname').extra(where = [where_clause])
t = songs.count() # sql error is caused by this call to count()
I know that the query to get the actual data works just fine. It is only the COUNT that is not working. The work around was to get the count manually (by writing an sql statement that gets the count, and executing the statement 'manually' using connection.cursor().execute(sql)).
The two arguments passed to "select related" are both valid.
The problem is that the 'where' clause uses the two tables that are specified by the "select_related" call, but the count() function apparently ignores the select_related call.
Perhaps the QuerySet.count() function should be smart enough to take the "select_related" args into consideration when generating the COUNT sql.
I did try to specify missing tables using tables=[...] as an argument to extra(), but I couldn't get it to work with both the count() and the actual data query.