Django left join or analog

Question

I can't figure out how to use Django ORM for left joins, please see case below.

My models are (please, don't mind native language):

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('трудочасы')
    amount = models.DecimalField('выработка', max_digits=10, decimal_places=2)
    note = models.TextField('заметка', blank=True, null=True)
    is_auto = models.BooleanField(
        'признак автоматического расчета', default=False)

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

I would like to make following selection:

Get all the Timeperiod with filters on start_date and end_date
Get related Production with filter on project (if any exists for this particular period)

Pass it to template_processor and render it as a table (in for loop)

I know how to filter queryset, and know how to get related queryset, but I don’t understand how to filter my RELATED queryset

UPDATE

I want queryset that should be just like this SQL query:

SELECT * 
FROM collector_timeperiod tp
LEFT JOIN collector_production AS p ON p.timeperiod_id = tp.id 
                                    AND p.project_id = 3

user_67579 · Answer

You can filter on the related model with timeperiod__…, for example:

Production.objects.filter(
    timeperiod__start_date__gte='2022-3-1',
    timeperiod__end_date__lte='2022-3-31'
)

or if you want to retrieve the Projects:

Project.objects.filter(
    production__timeperiod__start_date__gte='2022-3-1',
    production__timeperiod__end_date__lte='2022-3-31'
).distinct()

The .distinct() call ^[Django-doc] will avoid retrieving the same Project that many times as there are matching Timeperiods.

Django left join or analog

1 Answers