Filter QuerySet by checking if pk is in annotated array

[bleak ferry]

I want to be able to filter a queryset based on the pk's existence in an annotated array, like the title says.
Example is below. Please note this is a heavily simplified example.

User.objects.annotate(
                faves=ArraySubquery(
                    Favourite.objects.filter(user=OuterRef("pk")).values("object_id")
                )
            ).filter(faves__len__gt=1).filter(
                Exists(
                    Availability.objects.filter(
                        facility_id__in=OuterRef("faves"), cancelled=False
                    )
                )
            )

However, when I try to do this I get the following error:

ProgrammingError: operator does not exist: integer = integer[]
LINE 1: ...U0 WHERE (NOT U0."cancelled" AND U0."facility_id" IN (ARRAY(...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

There's something about ArrayAgg or ArraySubquery that doesn't play nice with IN checks. Does anyone know a way around this?

[proper cave]

Which dbms are you using? I know Postgres supports Arrays but not sure about others.

Arrays in Postgres don't support the IN, though something similar can be achieved with the contains operator (@>). Django exposes this with the contains lookup. https://docs.djangoproject.com/en/5.0/ref/contrib/postgres/fields/#contains

[bleak ferry]

I'm on postgres.

Ah. swap the LHS and RHS and use contains. I'll try that.

[bleak ferry]

Interesting, still doesn't work but different error

ProgrammingError: cannot cast type integer to integer[]
LINE 1: ...) ORDER BY V0."created" ASC) @> (U0."facility_id")::integer[...

the query I used:

User.objects.annotate(
                faves=ArraySubquery(
                    Favourite.objects.filter(user=OuterRef("pk")).values("object_id")
                )
            ).filter(faves__len__gt=1).filter(
                Exists(
                    Availability.objects.annotate(
                        fave_ids=models.ExpressionWrapper(
                            OuterRef("faves"),
                            output_field=ArrayField(models.IntegerField()),
                        )
                    ).filter(fave_ids__contains=F("facility_id"), cancelled=False)
                )
            )

I wonder if it's just not supported

[proper cave]

contains only works with arrays on both sides. The right side here is an integer.

I'm not as familiar with the Django ORM as I am with SQL, so bear with me. I think if you use a regular SubQuery instead of an ArraySubquery you can just use in again.

[bleak ferry]

oh duh, the error is telling me exactly that. good catch. I'll try again

Hmm, not sure what you mean with the second part. I can't use a regular Subquery because they can only return a single value