django-subquery-count.md

This was close to useful but the SQL generated has problems with ORDER BY clauses.

The subquery was necessary to avoid GROUP BY problems.

class AG_DATES_OVERLAP(Func):
    function = 'dbo.AG_DATES_OVERLAP'
    template = "%(function)s(%(expressions)s)"

    def __init__(self, *expressions, **extra):
        super(AG_DATES_OVERLAP, self).__init__(*expressions, output_field=IntegerField(), **extra)


class LicenseHistoryFilters(filters.FilterSet):
    date_allocated = filters.DateFilter(method="filter_date_allocated")
    date_returned = filters.CharFilter(method="filter_date_returned")

    def filter_date_returned(self, qs, name, value):
        return qs

    def filter_date_allocated(self, qs, name, value):
        date_allocated = self.data.get('date_allocated')
        date_returned = self.data.get('date_returned')
        sqlf = AG_DATES_OVERLAP(Value(date_allocated),
                                Value(date_returned),
                                F('date_start'),
                                F('date_end'))
        subq = qs.annotate(dates_overlap=sqlf).filter(dates_overlap=1)
        qs2 = qs.filter(id__in=Subquery(subq.values('id')))

        # import pdb; pdb.set_trace()
        return qs2

    class Meta:
        model = AgLicenseHistory
        fields = ['cm_person', ]