0

If an ORM query includes a subquery (easily accomplished by the RawSQL expression or the new Subquery expression in Django 1.11), Django ORM's count method appends that to its group by clause.

Here's a simplified example:

qs = Activity.objects.filter(...).annotate(feed_date=RawSQL('SELECT MAX(feed_date) FROM core_track WHERE ...'))
qs.count()

This will generate something like below: 

SELECT COUNT_BIG(*) FROM 
(SELECT [core_activity].[id] AS Col1, 
(SELECT MAX(feed_date) FROM core_track WHERE activity_id=id) AS [feed_date] 
FROM [core_activity] WHERE (...) 
GROUP BY [core_activity].[id], (SELECT MAX(feed_date) FROM core_track WHERE activity_id=id))

Normally we use Postgres and it happily accepts & executes this sql. However, our new customer requires using SQL Server which doesn't like a subquery in the group by statement:

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause

I wonder if I am overlooking anything, or doing something wrong or otherwise if there's a sane workaround (such as a setting in Sql Server that allows this query to pass). Any suggestions?

shanyu
  • 9,536
  • 7
  • 60
  • 68
  • try this once Activity.objects.filter(...).annotate(feed_date=Count(Max(core_track__feed_date))) – Exprator Jun 07 '17 at 09:21
  • and import Count and Max – Exprator Jun 07 '17 at 09:21
  • @Exprator, the above query is just an example, don't take it seriously. We do need the subquery in reality. – shanyu Jun 07 '17 at 10:24
  • the subquery is being passed in the MAX filter – Exprator Jun 07 '17 at 10:25
  • note that SQL server isn't officially supported. https://docs.djangoproject.com/en/1.11/ref/databases/#using-a-3rd-party-database-backend there could very well be a bug in the third party libraries you are using. – e4c5 Jun 07 '17 at 14:30

0 Answers0