Skip to content

Instantly share code, notes, and snippets.

@maxtortime
Created October 23, 2018 06:50
Show Gist options
  • Save maxtortime/6c81e60a00d03a08bec0b6465dd2738e to your computer and use it in GitHub Desktop.
Save maxtortime/6c81e60a00d03a08bec0b6465dd2738e to your computer and use it in GitHub Desktop.
filter by annotated window
qs = ConditionOccurrence.objects.filter(
condition_concept_id__in=ConceptSet.objects.get(pk=1198).concepts.values(
'pk')).annotate(ordinal=Window(expression=RowNumber(), order_by=F(
'condition_start_date').asc(), partition_by=[F('person_id')])).filter(ordinal=1)
...
django.db.utils.NotSupportedError: Window is disallowed in the filter clause.
@maxtortime
Copy link
Author

SELECT * FROM (
SELECT *,
   ROW_NUMBER() OVER (PARTITION BY [CONDITION_OCCURRENCE].[person_id] ORDER BY [CONDITION_OCCURRENCE].[condition_start_date] ASC) AS [ordinal]
    FROM [CONDITION_OCCURRENCE] WHERE [CONDITION_OCCURRENCE].[condition_concept_id] IN (SELECT U0.[CONCEPT_ID] FROM [AI_CTS_concept] U0 
	INNER JOIN [AI_CTS_conceptset_concepts] U1 ON (U0.[CONCEPT_ID] = U1.[concept_id]) WHERE U1.[conceptset_id] = 1198)) A
	WHERE A.ordinal = 1

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment