Skip to content
Advertisement

Django Query with 3 tables

I’m hoping I can get a little guidance.

I’m trying to return data from 3 related tables in my template. In SQL, this is a simple approach, but the Django requirements have me stumbling.

I’d like to display information similar to this:

WaiverAdult.first_name  CheckIn.checkintime
WaiverMinor.first_name  CheckIn.checkintime
WaiverAdult.first_name  CheckIn.checkintime
WaiverMinor.first_name  CheckIn.checkintime
WaiverMinor.first_name  CheckIn.checkintime

Here are a simplified representation of the models with the relationships defined.

class WaiverAdult(models.Model):
    first_name = models.CharField(max_length=50, blank=True)

class WaiverMinor(models.Model):
    first_name = models.CharField(max_length=50, blank=True)
    parent = models.ForeignKey(WaiverAdult, on_delete=models.CASCADE)

class CheckIns(models.Model):
    adult = models.ForeignKey(WaiverParent, on_delete=models.CASCADE, blank=True, null=True)

    minor = models.ForeignKey(WaiverChild, on_delete=models.CASCADE, blank=True, null=True)

    checkintime = models.DateTimeField(auto_now_add=True)

Here is my simplified view:

class WaiverListView(ListView):

    waiver_adults = WaiverAdult.objects.all().prefetch_related(
        'waiverminor_set').order_by('created')

    queryset = waiver_adults

    context_object_name = "waiver_list"

    template_name = 'waiver/waiver_list.html'

And, this is my template.

{% for adult in waiver_list %}

    <tr>
        <td>{{adult.first_name}}</td>
        <td>insert the adult checkin time here</td>
    </tr>


    {% for child in adult.waiverminor_set.all %}

        <tr>
            <td>{{child.first_name}}</td>
            <td>insert the child checkin time here</td>
        </tr>

    {% endfor %}
    
{% endfor %}

I would be very appreciative of details in the explanations as I really want to understand how this all works.

Advertisement

Answer

Firstly, for every Foreign key you are creating I suggest you to add a related_name this way you specify the name of reverse relation ship between the children model and parent model in your case for example, your code should be:

class WaiverAdult(models.Model):
    first_name = models.CharField(max_length=50, blank=True)

class WaiverMinor(models.Model):
    first_name = models.CharField(max_length=50, blank=True)
    parent = models.ForeignKey(WaiverAdult, on_delete=models.CASCADE,related_name='minor_of_adult')

and let’s explain how does it work, you want to know and get all the minors of some adult, what you should do is specify the adult, and get all minor related to that adult, in code context (try it in django shell, using python manage.py shell):

adult = WaiverAdult.objects.get(first_name='cbirch') #specifying the adult
adult.minor_of_adult.all() #notice how we access the minor using the related_name

same thing apply to the last model CheckIns.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement