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