QuerySet.count() generates invalid sql when used with select_related() and extra(where=[customWhereClause])
|Reported by:||eddified||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.3|
|Cc:||jamey, j||Triage Stage:||Accepted|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Description (last modified by aaugustin)
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('album__genre__genre__id', 'artist__name').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.
Change History (9)
comment:1 Changed 5 years ago by eddified
- Needs documentation unset
- Needs tests unset
- Patch needs improvement unset
comment:3 Changed 2 years ago by jamey
- Cc jamey added
- Component changed from Uncategorized to Database layer (models, ORM)
- Easy pickings unset
- Resolution duplicate deleted
- Severity set to Normal
- Status changed from closed to reopened
- Type set to Bug
- UI/UX unset
- Version changed from 1.0 to 1.3
comment:5 Changed 21 months ago by aaugustin
- Description modified (diff)