id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
33751	Multiple Conditions on LEFT JOIN - FilteredRelations not working?	Thorben Luepkes	nobody	"I think I am running into a similar problem here, where I would like to append multiple `conditions` onto my join:
I will try to be precise with this as much as possible. 
Imagine these two models. whose relation was set up years ago:


{{{

class Event(models.Model):
    instance_created_date = models.DateTimeField(auto_now_add=True)
    car = models.ForeignKey(Car, on_delete=models.CASCADE, related_name=""car_events"")
    ...
    a lot of normal text fields here, but they dont matter for this problem.
}}}


and


{{{

class Car(models.Model):
    a lot of text fields here, but they dont matter for this problem.
    hide_from_company_search = models.BooleanField(default=False)
    images = models.ManyToManyField(Image, through=CarImage)
}}}



Lets say I want to query the amount of events for a given car:
{{{
def get_car_events_qs() -> QuerySet:
    six_days_ago = (timezone.now().replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=6))
    cars = Car.objects.prefetch_related(
        'car_events',
    ).filter(
        some_conditions_on_fields=False,
    ).annotate(
        num_car_events=Count(
            'car_events',
            filter=Q(car_events__instance_created_date__gt=six_days_ago), distinct=True)
    )

    return cars
}}}


The really tricky part for this is the performance of the query: `Cars` has `450.000` entries, and `Events` has `156.850.048`. All fields that I am using to query are indexed. The query takes around 4 minutes to complete, depending on the db load. It took 18 minutes before adding the indicies.

This above ORM query will result in the following sql:


{{{
SELECT
  ""core_car"".""id"",
  		COUNT(""analytics_carevent"".""id"") FILTER (WHERE (""analytics_carevent"".""event"" = 'view'
			AND ""analytics_carevent"".""instance_created_date"" >= '2022-05-10T07:45:16.672279+00:00'::timestamptz
			AND ""analytics_carevent"".""instance_created_date"" < '2022-05-11T07:45:16.672284+00:00'::timestamptz)) AS ""num_cars_view"",
	LEFT OUTER JOIN ""analytics_carevent"" ON (""core_car"".""id"" = ""analytics_carevent"".""car_id"")
WHERE 
 ... some conditions that dont matter
GROUP BY
	""core_car"".""id""
}}}



I somehow suspect this `FILTER` to be a problem.
I tried with

{{{
.annotate(num_car_events=Count('car_events'))
}}}

and moving the `car_events__instance_created_date__gt=six_days_ago` into the `filter`:

{{{
.filter(some_conditions_on_fields=False, car_events__instance_created_date__gt=six_days_ago)
}}}


But of course this would filter out Cars with no Events, which is not what we want - but it is super fast! 
I fiddled a bit with it in raw sql and came to his nice working example, that I now would like to write into ORM, since we dont really want to use rawsql. This query takes `2.2s`, which is in our acceptable boundary, but faaaaar less than the 18minutes.


{{{
SELECT
	""core_car"".""id"",
	COUNT(DISTINCT ""analytics_carevent"".""id"") AS ""num_cars_view"",
FROM
	""core_car""
	LEFT JOIN ""analytics_carevent"" ON (""core_car"".""id"" = ""analytics_carevent"".""car_id"" AND ""analytics_carevent"".""event"" = 'view' AND ""analytics_carevent"".""instance_created_date"" > '2022-05-14T00:00:00+02:00'::timestamptz
		AND ""analytics_carevent"".""instance_created_date"" <= '2022-05-15T00:00:00+02:00'::timestamptz)
	
WHERE (some conditions that dont matter)
GROUP BY ""core_car"".""id"";
}}}



My question now is:
How can I make the above query into the ORM?
I need to put the ""filter"" or conditions onto the `left join`. If I just use `filter()` it will just put it into the `where` clause, which is wrong.
I tried: 


{{{
 two_days_ago = (timezone.now().replace(hour=0, minute=0, second=0, microsecond=0) - timedelta(days=2))
    cars = Car.objects.prefetch_related(
        'car_events',
    ).filter(some_filters,)
}}}

and

{{{
cars = cars.annotate(events=FilteredRelation('car_events')).filter(car_events__car_id__in=cars.values_list(""id"", flat=True), car_events__instance_created_date__gt=six_days_ago)
}}}


But I dont think this is quite correct. I also need the count of the annotation.
I also tried with

{{{
cars = cars.annotate(events=FilteredRelation('car_events', condition=Q(car_events__car_id__in=ads.values_list(""id"", flat=True)))).filter(events__instance_created_date__gt=six_days_ago)
}}}

But this results in an inner join, which i dont think is wanted here

Using Django 4 and latest python release as of this writing. :)

Thanks a lot!

**TLDR: Putting a filter or conditions on `LEFT JOIN` in django, instead of `queryset.filter()`**
"	Uncategorized	closed	Utilities	4.0	Normal	duplicate	orm, django, join		Unreviewed	0	0	0	0	0	0