The .union creates a second query, you would need to add the annotation method after the filter in the union clause.
e.g., return self.filter(a__b=user).union(self.filter(c__d=user).annotate(...))
(I know that’s not what you want.)
What about if, instead of a union, you write this as an or between two Q objects?
e.g., Foo.objects.filter( Q(a__b=user) | Q(c__d=user) ).annotate(...).distinct()
Yes, this is possible and it’s (roughly) what I’m doing now. Unfortunately on Postgres or queries involving joins can be quite slow (Avoid OR for better PostgreSQL query performance - CYBERTEC) and the tables involved can get quite large. For now it’s fast enough to use, but not sure for how much longer.
Perhaps I can find some sneaky way to copy any annotations, if they exist, to the second query
Note that the issue they are addressing in that article is a case where the OR clause is comparing values from two different tables - that’s not the case here. I don’t think you would have the same issues when it’s two different filters on the same table.
A quick look at the explain on a (limited size) test database shows that PostgreSQL expects the OR version to be significantly faster than the UNION.
But if you really want to be sure, you may want to look at the explain (or even run an explain analyze) to see what the query planner has to say about your situation.
It wouldn’t even need to be sneaky. You could add it as a keyword parameter to the function and add it to the function call if necessary.
In your class, are a and b foreign keys to other tables, or are they the related named for foreign keys in those other tables? If the latter, then yes - the article is on point. If the former (the assumption I had made), then it’s a different issue.
In either case, you would still want to run an explain analyze on the two different query formations to identify which one will be faster on average for your databases, configurations, and versions of PostgreSQL. (2018 was PostgreSQL 11, and there are always changes and improvements over time.)
If the schemas are the same (same indexes, etc) and you have a “reasonable” amount of data, then the explains are likely to give you a good baseline. But yes, the analyze isn’t going to be of much value until you’re at scale.
As soon as you have a reverse FK relationship in there, then yes, you’re in the category similar to what the article is talking about. If every link in that chain is a forward reference, then it is a reference to a single field - you’re not examining multiple rows to identify a candidate row. It seems to me that the evaluation of multiple rows for a single row in the base table that would cause the combinatorial complexity for the search.
If the filter were egg__chicken__coop__farmer=user, then I can follow this chain to identify the farmer by referencing multiple tables - but a single row in each table. I’m not dealing with a set of rows at any point in this chain. On the other hand, if my filter is coop__chickens__eggs=double_yolk, then I’m having to evaluate multiple rows to identify which coops I got my eggs with two yolks from - a much different situation.