I am having some difficulty linking/obtaining data from 3 models all related to one another. I'm thinking i've either designed them incorrectly or I don't fully understand how to work with parental and many-to-many fields.

There are 3 models (full models shown at end of question):

  • ClubManager Where club names are entered
  • ClubTimetables The timetable, relevant to the ClubManager
  • DaysOfTheWeek Mon-Sun, used by ClubTimetables

For which I am trying to retrieve information from all of them: the name of the club, its timetable(s) and for each timetable the days of the week it's linked to. However, I can't seem to do it in a way that doesn't constantly hit the database.

First thing I tried was to get ClubManager and select_related it's timetable but couldn't figure out how to preselect the DaysOfTheWeek it was connected with.

The approach I am trying now is to instead get the ClubTimetable and use select_related and prefetch_related on both the models it's linked with, like so

timetable = ClubTimetables.objects.select_related('attached_to').prefetch_related('weekday')

for t in timetable:
    print(t.attached_to.name)

This results in such a query

connection.queries
 
[{'sql': 'SELECT "club_clubtimetables"."id", "club_clubtimetables"."sort_order", "club_clubtimetables"."address_line_1", "club_clubtimetables"."address_line_2", "club_clubtimetables"."address_city", "club_clubtimetables"."address_county", "club_clubtimetables"."address_country", "club_clubtimetables"."map_coord_lat", "club_clubtimetables"."map_coord_lon", "club_clubtimetables"."attached_to_id", "club_clubtimetables"."start_time", "club_clubtimetables"."end_time", "club_clubmanager"."id", "club_clubmanager"."name" FROM "club_clubtimetables" INNER JOIN "club_clubmanager" ON ("club_clubtimetables"."attached_to_id" = "club_clubmanager"."id") ORDER BY "club_clubtimetables"."sort_order" ASC',
  'time': '0.020'},
 {'sql': 'SELECT ("club_clubtimetables_weekday"."clubtimetables_id") AS "_prefetch_related_val_clubtimetables_id", "core_daysoftheweek"."id", "core_daysoftheweek"."weekday" FROM "core_daysoftheweek" INNER JOIN "club_clubtimetables_weekday" ON ("core_daysoftheweek"."id" = "club_clubtimetables_weekday"."daysoftheweek_id") WHERE "club_clubtimetables_weekday"."clubtimetables_id" IN (1, 2)',
  'time': '0.002'}]

Which looks correct(?)

But, trying to obtain the related weekdays hits the database each time.

for t in timetable:
    for training in t.weekday.values():
        print(training['weekday'])

# Tuesday
# Sunday

In [14]: connection.queries
Out[14]: 
[{'sql': 'SELECT "core_daysoftheweek"."id", "core_daysoftheweek"."weekday" FROM "core_daysoftheweek" INNER JOIN "club_clubtimetables_weekday" ON ("core_daysoftheweek"."id" = "club_clubtimetables_weekday"."daysoftheweek_id") WHERE "club_clubtimetables_weekday"."clubtimetables_id" = 1',
  'time': '0.001'},
 {'sql': 'SELECT "core_daysoftheweek"."id", "core_daysoftheweek"."weekday" FROM "core_daysoftheweek" INNER JOIN "club_clubtimetables_weekday" ON ("core_daysoftheweek"."id" = "club_clubtimetables_weekday"."daysoftheweek_id") WHERE "club_clubtimetables_weekday"."clubtimetables_id" = 2',
  'time': '0.000'}]

# ran again...

for t in timetable:
    for training in t.weekday.values():
        print(training['weekday'])

# Tuesday
# Sunday

In [16]: connection.queries
Out[16]: 
[{'sql': 'SELECT "core_daysoftheweek"."id", "core_daysoftheweek"."weekday" FROM "core_daysoftheweek" INNER JOIN "club_clubtimetables_weekday" ON ("core_daysoftheweek"."id" = "club_clubtimetables_weekday"."daysoftheweek_id") WHERE "club_clubtimetables_weekday"."clubtimetables_id" = 1',
  'time': '0.001'},
 {'sql': 'SELECT "core_daysoftheweek"."id", "core_daysoftheweek"."weekday" FROM "core_daysoftheweek" INNER JOIN "club_clubtimetables_weekday" ON ("core_daysoftheweek"."id" = "club_clubtimetables_weekday"."daysoftheweek_id") WHERE "club_clubtimetables_weekday"."clubtimetables_id" = 2',
  'time': '0.000'},
 {'sql': 'SELECT "core_daysoftheweek"."id", "core_daysoftheweek"."weekday" FROM "core_daysoftheweek" INNER JOIN "club_clubtimetables_weekday" ON ("core_daysoftheweek"."id" = "club_clubtimetables_weekday"."daysoftheweek_id") WHERE "club_clubtimetables_weekday"."clubtimetables_id" = 1',
  'time': '0.001'},
 {'sql': 'SELECT "core_daysoftheweek"."id", "core_daysoftheweek"."weekday" FROM "core_daysoftheweek" INNER JOIN "club_clubtimetables_weekday" ON ("core_daysoftheweek"."id" = "club_clubtimetables_weekday"."daysoftheweek_id") WHERE "club_clubtimetables_weekday"."clubtimetables_id" = 2',
  'time': '0.001'}]

Perhaps it is because I'm using .values()? But I don't know how else to obtain those values.

My models currently look something like this

# ClubManager

@register_snippet
class ClubManager(ClusterableModel):

    name = models.CharField('Club name',
                            max_length=255,
                            help_text='The name of the club.')

    panels = [
        FieldPanel('name'),
        InlinePanel('club_timetable', heading='Timetable Information')
    ]
# ClubTimetables

class ClubTimetables(Orderable, AddressBase):

    attached_to = ParentalKey(
        'club.ClubManager', related_name='club_timetable')

    weekday = models.ManyToManyField(DaysOfTheWeek)

    start_time = models.TimeField()
    end_time = models.TimeField()

    panels = [ ... ] + AddressBase.panels
# DaysOfTheWeek

class DaysOfTheWeek(models.Model):

    weekday = models.CharField(max_length=9)

    def __str__(self):
        return self.weekday

What can I do to fetch all relevant data from these models in one go without repeating db calls?
Or, if I designed these models incorrectly, in what way could build them to use it in the way intended?