Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

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', ]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment