Generating counts of sales over date ranges with filtering

Using Django Using the ORM
1

I’ve having trouble filtering a TextField in a subqueries queryset by the parents related object field.
I’m using django-generate-series on Postgres to construct a series of DateTimeRangeFields to then filter the Sales model in a subquery.

models:

class Sales(OrderItem):
    class Meta:
        proxy = True
    ...

class OrderItem(models.Model):
    options = models.TextField(null=False, blank=True)
    order = models.ForeignKey(Order, related_name="items", on_delete=models.PROTECT)
    content_type = models.ForeignKey(
        ContentType, limit_choices_to=limit, on_delete=models.PROTECT
    )
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey("content_type", "object_id")
    ....

class PriceCategory(models.Model):
    title = models.CharField(max_length=250, unique=True)
    categories = models.ManyToManyField("shop.Category")
    ...

class Category(models.Model):
    title = models.CharField(blank=True, max_length=100)
    group = models.ForeignKey(
        CategoryGroup, related_name="categories", on_delete=models.PROTECT
    )

class Product(models.Model):
    categories = models.ManyToManyField(Category, related_name="music_products")
    sales = GenericRelation("reports.Sales")
    ...

views.py:

categories = Category.objects.filter(group__title="Format")

date_sequence = generate_series(
    start=data.get("start"),
    stop=data.get("end"),
    step=step,
    output_field=DateTimeRangeField,
)

sales = (
    Sales.objects.filter(
        options=OuterRef("category__pricecategory__title"),
        order__trans_time__gte=OuterRef("term__startswith"),
        order__trans_time__lt=OuterRef("term__endswith"),
    )
    .annotate(
        count=Func("id", function="COUNT", output_field=models.IntegerField())
    )
    .values("count")
)

dates = date_sequence.annotate(category=OuterRef("id"), sales=sales).values(
    "sales"
)

categories.annotate(sales=ArraySubquery(dates)).values("title", "sales")

This works if I remove:

options=OuterRef("category__pricecategory__title")

from the Sales model filter, but I need to filter by the type of Product that was sold.
The error I am getting is:

AttributeError: 'ResolvedOuterRef' object has no attribute 'get_transform'

and the traceback highlights this line:

date_sequence.annotate(category=OuterRef("id"), sales=sales)

Can anyone suggest what the problem might be and how I can fix it?

Another thing I tried was passing the PriceCategory’s to the sales subquery as an ArrayAgg like this:

sales = (
    Sales.objects.filter(
        options__in=OuterRef("pricecategories"),
        order__trans_time__gte=OuterRef("term__startswith"),
        order__trans_time__lt=OuterRef("term__endswith"),
    )
    .annotate(
        count=Func("id", function="COUNT", output_field=models.IntegerField())
    )
    .values("count")
)

dates = date_sequence.annotate(
    pricecategories=ArrayAgg(OuterRef("pricecategory__title")), sales=sales
).values("sales")

categories.annotate(sales=ArraySubquery(dates)).values("title", "sales")

But I ran into an issue with filtering the Sales model which generated the error:

ProgrammingError: operator does not exist: text = character varying[]
LINE 1: ...s_time" < (upper(V0."term"))) HAVING U0."options" IN (ARRAY_...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Any help appreciated!

2

OuterRef is a class for using the Subquery class.
You are using the wrong query.
If you want to compare values ​​that exist in a specific field, you should use the F class, not OuterRef.

It may be better to be clear about your desired outcome and provide a model.
That way, other people can see your model and write the queries you want.

3

Thanks for the reply. I think I have found a solution now.
I have removed the OuterRef from the Sales model filter and instead I am annotating the Sales with the parent category before filtering by date range with the OuterRef filter, like this:

price_categories = PriceCategory.objects.filter(
    title=OuterRef("options")
).values("categories__id")[:1]

sales = (
    Sales.objects.annotate(format=Subquery(price_categories))
    .filter(
        format=OuterRef("category"),
        order__trans_time__gte=OuterRef("term__startswith"),
        order__trans_time__lt=OuterRef("term__endswith"),
    )
    .annotate(
        count=Func(
            "id", function="COUNT", output_field=models.BigIntegerField()
        )
    )
    .values("count")
)