Left join or analog

Using Django Using the ORM
1

Is there LEFT JOIN or some analog in Django?

Here is my models (please, don’t mind native language):

class Timeperiod(models.Model):
    '''Периоды.'''
    start_date = models.DateField('дата начала')
    end_date = models.DateField('дата окончания')
    target_duration = models.DurationField('длительность')
    is_open = models.BooleanField('открыт', default=True)

class Production(models.Model):
    '''Выработка.'''
    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?

2

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
e.g:

timeperiods = Timeperiod.objects.filter(...).prefetch_related(
  Prefetch(
    'production', 
    to_attr='prods', 
    queryset=Production.objects.filter(project=some_project_instance)
  )
)

If a_timeperiod is an instance of timeperiods, you can then access the related set of Production instances through a_timeperiod.prods.

3

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()

4

Ahh, you may need to use the related manager name in the Prefetch. (e.g. ‘production_set’) See QuerySet API reference | Django documentation | Django

5

Yes, thank you Ken, now I get it

6

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 
    collector_timeperiod tp

LEFT JOIN collector_production p ON 
    p.timeperiod_id = tp.id

WHERE
    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

7

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 collector_production row.

8

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.

9

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
    collector_timeperiod tp

LEFT JOIN collector_production AS p ON p.timeperiod_id = tp.id AND p.project_id = 3
10

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:
print(periods[0].production)

gives me:
'Timeperiod' object has no attribute 'production'

So what the point of that annotation

11

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 test.

qs = MyModel.objects.all().annotate(test=3)
qs[0].test == 3  # is True

See all the examples in the “Cheat Sheet”

12

Yeah, I get it, but it seems that is not working with FilteredRelation, no matter am I using .objects.annotate or .objects.all().annotate()

13

What’s not working? I’m not seeing all the pieces of what you’re trying to do here.

14

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'

15

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.)