Multiple relationship lookup query

Using Django Using the ORM
1

Try as I might, I just can’t get this working in Django ORM as the relationship mapping just doesn’t make any logical sense and would welcome guidance on how to complete this.

Here is the SQL I am unable to convert:

select leftteamcode_id as id
from ksail_events_scheduletemplateraces 
where scheduleTemplate_id = 1
UNION
select rightteamcode_id as id
from ksail_events_scheduletemplateraces 
where scheduleTemplate_id = 1
order by id

Here are the models:

class ScheduleTemplate(models.Model):
    name = models.CharField(max_length=255)
    description = models.TextField(blank=True)
    no_teams = models.IntegerField(blank=True)
    no_flights = models.IntegerField(blank=True)

    def __str__(self):
        return self.name

class ScheduleTemplateRaces(models.Model):
    scheduleTemplate = models.ForeignKey(
        'scheduleTemplate',
        related_name = 'race_in_template',
        on_delete = models.PROTECT
    )
    racenum = models.IntegerField()
    leftteamcode = models.ForeignKey(
        'scheduleTemplateTeams',
        related_name = 'left_team_in_racetemplate',
        on_delete = models.PROTECT
    )
    rightteamcode = models.ForeignKey(
        'scheduleTemplateTeams',
        related_name = 'right_team_in_racetemplate',
        on_delete = models.PROTECT
    )

class ScheduleTemplateTeams(models.Model):
    name = models.CharField(max_length=100)

    def __str__(self):
        return self.name

This is the view I have created:

def createRacesinStage(request, stageid):
    #implemented by a form that needs to manage the mappings for flights, boats, teams
    renderer_classes = [TemplateHTMLRenderer]
    template = loader.get_template('ksail_events/create_races_in_stage_form.html')

    #get the relevant querysets
    stageobj = Stage.objects.get(id = stageid)
    eventid = getattr(stageobj, 'id')
    scheduletempid = getattr(stageobj, 'based_on_schedule_id')
 
    schedteamlist = ScheduleTemplateTeams.objects.raw("select leftteamcode_id as id "
                                                      "from ksail_events_scheduletemplateraces "
                                                      "where scheduleTemplate_id = 1 "
                                                      "UNION "
                                                      "select rightteamcode_id as id "
                                                      "from ksail_events_scheduletemplateraces "
                                                      "where scheduleTemplate_id = 1 "
                                                      "order by id ")

    enteredteams = Entry.objects.filter(event_id = eventid).all().prefetch_related('team')

    #If this is a POST request then process the form
    if request.method == 'POST':
        form = createRacesinStageForm(request.POST)

        #Check that form is valid
        if form.is_valid():
            #do something
            return HttpResponse(reverse('EventStagesView'), args=[eventid])
    
    #If this is a GET or any other request
    else:
        form = createRacesinStageForm()

    context = {
        'form': form,
        'schedteams': schedteamlist,
        'enteredteams': enteredteams,
        'stageobj': stageobj
    }
    return HttpResponse(template.render(context, request))

The use case is as follows:
I need to find all the template team names and ids that have been used in the template races either as the left or right team but as a single list of names. This list is then presented on a form where the results of which will be written to a completely different table and so needs to match the scheduletemplateteams model and not scheduletemplateraces model.

Many thanks

Paul

2

I’m not sure I quite follow you here, so I might be missing something.

qs1 = ScheduleTemplateRaces.objects.values('leftteamcode_id', 'leftteamcode__name')
qs2 = ScheduleTemplateRaces.objects.values('rightteamcode_id', 'rightteamcode__name')
qs3 = qs1.union(qs2)

This gives you a queryset of a list of dict, where each dict has the keys leftteamcode_id and leftteamcode__name, even though both leftteamcode and rightteamcode populate those entries.

3

Thanks for another fast response.

The only adjustment I needed to make was to add the sort back in

qs3 = qs1.union(qs2).order_by('leftteamcode_id')