Is there LEFT JOIN or some analog in Django?
Here is my models (please, don’t mind native language):
start_date = models.DateField('дата начала')
end_date = models.DateField('дата окончания')
target_duration = models.DurationField('длительность')
is_open = models.BooleanField('открыт', default=True)
project = models.ForeignKey(
Project, on_delete=models.PROTECT, verbose_name='проект')
timeperiod = models.ForeignKey(
Timeperiod, on_delete=models.PROTECT, verbose_name='период')
time = models.DurationField('трудочасы')
ammount = models.DecimalField('выработка', max_digits=10, decimal_places=2)
note = models.TextField('заметка', blank=True, null=True)
is_auto = models.BooleanField(
'признак автоматического расчета', default=False)
I would like to select all existing periods (filtering by dates) AND related productions (if any, filtering by project).
I understand how to filter, but I can’t figure out how to get what I want? Should I somehow make two querysets and then «merge» them? Or maybe I should use union instead?
Since this is a reverse access of a ForeignKey relationship, Django does not produce a “single query” version of this.
You can query on Timeperiod with the prefetch_related clause to retrieve the Production instances, and then filter the Production instances when accessing them through the related manager.
See QuerySet API reference | Django documentation | Django
timeperiods = Timeperiod.objects.filter(...).prefetch_related(
a_timeperiod is an instance of
timeperiods, you can then access the related set of Production instances through
I got the idea, but I can’t figure out what am I doing wrong
Cannot find 'production' on Timeperiod object, 'production' is an invalid parameter to prefetch_related()
Ahh, you may need to use the related manager name in the Prefetch. (e.g. ‘production_set’) See QuerySet API reference | Django documentation | Django
Yes, thank you Ken, now I get it
For the record - I would like to make queryset that looks like exactly this join
I want queryset that should be just like this SQL query:
SELECT * FROM
LEFT JOIN collector_production p ON
p.timeperiod_id = tp.id
tp.start_date >= '2022-02-28' AND
p.project_id = 1
Upper solution working and great, but I really think there must be easier ways to make such queries
I don’t think the left join there helps you any since that query is followed up by a filter on
project_id which excludes any rows of
collector_timeperiod that don’t have a corresponding
Or, if that is what you want, there’s a way to craft that query as well - but it’s not the requirement as defined in the original post.
I m really sorry - that was late night when I write my yesterday’s post
Really my SQL query analog will be (with AND condition within the join):
SELECT * FROM
LEFT JOIN collector_production AS p ON p.timeperiod_id = tp.id AND p.project_id = 3
Maybe somehow FilteredRelation can suit this need:
periods = Timeperiod.objects.annotate(test=FilteredRelation(‘production’, condition=Q(production__project=3)))
But, I don’t understand how to access objects annotated this way, because something like:
'Timeperiod' object has no attribute 'production'
So what the point of that annotation
You reference the annotation via the name on the left side of the assignment statement within the annotate clause.
e.g. If you have
MyModel.objects.all().annotate(test=3), then every instance has a new attribute named
qs = MyModel.objects.all().annotate(test=3)
qs.test == 3 # is True
See all the examples in the “Cheat Sheet”
Yeah, I get it, but it seems that is not working with FilteredRelation, no matter am I using .objects.annotate or .objects.all().annotate()
What’s not working? I’m not seeing all the pieces of what you’re trying to do here.
If I do it like:
periods = Timeperiod.objects.annotate.all()(test=FilteredRelation(‘production’, condition=Q(production__project=3)))
it gives me:
'Timeperiod' object has no attribute ’test'
Annotate is a function - the parameters need to be applied to the annotate method.
Always keep in mind that what you’re writing is Python. (Some rather sophisticated Python under the hood, but still Python.)