Is it possible to tell Q to work with different related tables ?

quertenmont · April 12, 2024, 2:51pm

Hello,

I am facing an issue with combining Q func applying filters on related tables…

The problem is pretty simple to describe, I have two filters that I want to combine in an AND
filtA = Q(related__tags=A)
filtB = Q(related__tags=B)

combining them into filtAB
filtAB = filtA & filtB

I then want to count how many rows are passing this combine filter:
myTable.objects.filter(filtAB).count()

unfortunately, the result is incorrect, and I know I could solve the issue with consecutive filters
myTable.objects.filter(filtA).filter(filtB).count()
this lead to the correct count.

Unfortunately, I really need/want to use a combined Q filter: filtAB
Is there a way to indicate to the Q object that I want a new table join on my “related” table, instead of reusing the same join from the filtA in filtB

Thanks in advance for your help and suggestion.
Loic

PS: I didn’t find anything about this in the Q documentation, so I guess it’s really not mainstream.

KenWhitesell · April 12, 2024, 3:13pm

I can’t recreate the symptoms you are describing from the information provided so far. When I generate these types of queries in my lab, I’m getting the same SQL statement generated in both cases.

Now, this could be related to how the models are defined or the fields involved. It would be helpful if you posted a minimal example that can demonstrate this difference. (It would be even more helpful if you posted the query being generated in each case.)

quertenmont · April 12, 2024, 4:10pm

Maybe I over simplified the example:

Here is my real example with the full query

new_filter_58 = Q(lang__in=["en", "fr", "nl"]) & (
    Q(sources__source_groups__enabled=True, sources__source_groups__tags=58)|Q(keywords__tags=58)
)
new_filter_1 = Q(lang__in=["fr"]) & (
    Q(sources__source_groups__enabled=True, sources__source_groups__tags=1)|Q(keywords__tags=1)
)

Here is the correct answer:
pqs = PrimaryPost.objects.filter(created__gt=“2024-03-15”)
count_correct = pqs.filter(new_filter_1).filter(new_filter_58).distinct(“created”, “id”).count()
count_incorrect = pqs.filter(new_filter_1&new_filter_58).distinct(“created”, “id”).count()

query correctly generated is this one:

SELECT
	COUNT(*)
FROM
	(
		SELECT DISTINCT
			ON ("gdw_post_pri"."created", "gdw_post_pri"."id") "gdw_post_pri"."id" AS "col1",
			"gdw_post_pri"."lang" AS "col2"
		FROM
			"gdw_post_pri"
			LEFT OUTER JOIN "gdw_primarypostsources" ON (
				"gdw_post_pri"."id" = "gdw_primarypostsources"."post_id"
			)
			LEFT OUTER JOIN "gdw_source" ON (
				"gdw_primarypostsources"."source_id" = "gdw_source"."id"
			)
			LEFT OUTER JOIN "gdw_sourcegroup_sources" ON (
				"gdw_source"."id" = "gdw_sourcegroup_sources"."source_id"
			)
			LEFT OUTER JOIN "gdw_sourcegroup" ON (
				"gdw_sourcegroup_sources"."sourcegroup_id" = "gdw_sourcegroup"."id"
			)
			LEFT OUTER JOIN "gdw_sourcegroup_tags" ON (
				"gdw_sourcegroup"."id" = "gdw_sourcegroup_tags"."sourcegroup_id"
			)
			LEFT OUTER JOIN "gdw_primarypostkeyword" ON (
				"gdw_post_pri"."id" = "gdw_primarypostkeyword"."post_id"
			)
			LEFT OUTER JOIN "gdw_keyword" ON (
				"gdw_primarypostkeyword"."keyword_id" = "gdw_keyword"."id"
			)
			LEFT OUTER JOIN "gdw_keyword_tags" ON (
				"gdw_keyword"."id" = "gdw_keyword_tags"."keyword_id"
			)
			LEFT OUTER JOIN "gdw_primarypostsources" T12 ON ("gdw_post_pri"."id" = T12."post_id")
			LEFT OUTER JOIN "gdw_source" T13 ON (T12."source_id" = T13."id")
			LEFT OUTER JOIN "gdw_sourcegroup_sources" T14 ON (T13."id" = T14."source_id")
			LEFT OUTER JOIN "gdw_sourcegroup" T15 ON (T14."sourcegroup_id" = T15."id")
			LEFT OUTER JOIN "gdw_sourcegroup_tags" T16 ON (T15."id" = T16."sourcegroup_id")
			LEFT OUTER JOIN "gdw_primarypostkeyword" T18 ON ("gdw_post_pri"."id" = T18."post_id")
			LEFT OUTER JOIN "gdw_keyword" T19 ON (T18."keyword_id" = T19."id")
			LEFT OUTER JOIN "gdw_keyword_tags" T20 ON (T19."id" = T20."keyword_id")
		WHERE
			(
				"gdw_post_pri"."created" > '2024-03-15T00:00:00+00:00'::TIMESTAMPTZ
				AND "gdw_post_pri"."lang" IN ('fr')
				AND (
					(
						"gdw_sourcegroup"."enabled"
						AND "gdw_sourcegroup_tags"."tag_id" = 1
					)
					OR "gdw_keyword_tags"."tag_id" = 1
				)
				AND "gdw_post_pri"."lang" IN ('en', 'fr', 'nl')
				AND (
					(
						T15."enabled"
						AND T16."tag_id" = 58
					)
					OR T20."tag_id" = 58
				)
			)
		ORDER BY
			"gdw_post_pri"."created" DESC
	) SUBQUERY

query generated incorrectly is this one:

SELECT
	COUNT(*)
FROM
	(
		SELECT DISTINCT
			ON ("gdw_post_pri"."created", "gdw_post_pri"."id") "gdw_post_pri"."id" AS "col1",
			"gdw_post_pri"."lang" AS "col2"
		FROM
			"gdw_post_pri"
			LEFT OUTER JOIN "gdw_primarypostsources" ON (
				"gdw_post_pri"."id" = "gdw_primarypostsources"."post_id"
			)
			LEFT OUTER JOIN "gdw_source" ON (
				"gdw_primarypostsources"."source_id" = "gdw_source"."id"
			)
			LEFT OUTER JOIN "gdw_sourcegroup_sources" ON (
				"gdw_source"."id" = "gdw_sourcegroup_sources"."source_id"
			)
			LEFT OUTER JOIN "gdw_sourcegroup" ON (
				"gdw_sourcegroup_sources"."sourcegroup_id" = "gdw_sourcegroup"."id"
			)
			LEFT OUTER JOIN "gdw_sourcegroup_tags" ON (
				"gdw_sourcegroup"."id" = "gdw_sourcegroup_tags"."sourcegroup_id"
			)
			LEFT OUTER JOIN "gdw_primarypostkeyword" ON (
				"gdw_post_pri"."id" = "gdw_primarypostkeyword"."post_id"
			)
			LEFT OUTER JOIN "gdw_keyword" ON (
				"gdw_primarypostkeyword"."keyword_id" = "gdw_keyword"."id"
			)
			LEFT OUTER JOIN "gdw_keyword_tags" ON (
				"gdw_keyword"."id" = "gdw_keyword_tags"."keyword_id"
			)
		WHERE
			(
				"gdw_post_pri"."created" > '2024-03-15T00:00:00+00:00'::TIMESTAMPTZ
				AND "gdw_post_pri"."lang" IN ('fr')
				AND (
					(
						"gdw_sourcegroup"."enabled"
						AND "gdw_sourcegroup_tags"."tag_id" = 1
					)
					OR "gdw_keyword_tags"."tag_id" = 1
				)
				AND "gdw_post_pri"."lang" IN ('en', 'fr', 'nl')
				AND (
					(
						"gdw_sourcegroup"."enabled"
						AND "gdw_sourcegroup_tags"."tag_id" = 58
					)
					OR "gdw_keyword_tags"."tag_id" = 58
				)
			)
		ORDER BY
			"gdw_post_pri"."created" DESC
	) SUBQUERY

As you can see there is quite a few table joins involved in my example, but that does not matter too much, the source of the difference is that
qs.filter(A).filter(B) handle the query has different table joins (that is well documented in the documentation, don’t remember where though)
qs.filter(A&B) handle the query on related tables as a single join, and I would like to be able to tell my combined Q func A&B to use different joined tables instead of reusing the same table joins.

I hope the problem is clear and someone can help with this.
Loic,

charettes · April 12, 2024, 4:22pm

I suggest you look into using FilteredRelation annotations so you can target the same relationship in the same filter call but with different aliases and thus cause two distinct JOINs.

You could either create them with some empty condition=Q() (I really wish we had named this thing RelationAlias and made condition non-mandatory) or create them with conditions you expect them to be always filtered against.

Looking at the kind of things you are doing against multiple multi-valued relationship I have to warn you though, the ORM will likely return the wrong results no matter what you do under some circumstances.

quertenmont · April 15, 2024, 4:08am

It’s working thanks.

I absolutely didn’t think to use this object for this context. I agree that RelationAlias would be a better name.

Topic		Replies	Views
How to chain unknown number of Q expressions with OR relationship? Using the ORM	4	110	July 8, 2024
create sql with 2 table Using Django	1	529	June 30, 2020
Annotate with same table name Using the ORM	9	111	August 15, 2024
Join two filters of django-filters Forms & APIs	0	693	September 10, 2023
Order of combined Q objects seems to matter when querying against the same relationship Using the ORM	5	91	September 6, 2024

Is it possible to tell Q to work with different related tables ?

Related topics