Django Raw SQL - Left Outer Join efficiency and better method?

Question

I am attempting outer joins for 3 tables and was wondering if my method is correct and efficient?

Models:

class GuestCategory(models.Model):
    profile = models.ForeignKey(UserProfile)
    category = models.CharField(max_length=30)

    class Meta:
        db_table = 'guest_category'

class Guest(models.Model):
    profile = models.ForeignKey(UserProfile)
    guest_category = models.ForeignKey(GuestCategory, 
        null=True, blank=True, default = None)
    first_name = models.CharField(max_length=48, blank=False)
    last_name = models.CharField(max_length=48)
    invite_list = models.ManyToManyField(GuestList, through='RSVPList')

    class Meta:
        ordering = ('last_name', 'first_name')
        db_table = 'guest'

class GuestDetails(models.Model):
    guest = models.OneToOneField(Guest)
    email = models.EmailField(max_length=254, blank=True)
    ...
    country = models.IntegerField(choices=Country(), null=True, blank=True)

    class Meta:
        db_table = 'guest_details'

Views:

class GuestListView(LoginRequiredMixin, ListView):
    context_object_name = 'guest_list'
    template_name = 'guest/guest_list.html'

    def get_queryset(self):
        self.guests = Guest.objects.raw("SELECT \
            guest.id, \
            guest.first_name, \
            guest.last_name, \
            guest_details.email, \
            ...
            guest_details.country, \
            guest_category.category \
            FROM guest \
            LEFT OUTER JOIN guest_details \
            ON guest_details.guest_id = guest.id \
            LEFT OUTER JOIN guest_category \
            ON guest_category.id = guest.guest_category_id \
            WHERE guest.profile_id = %s", [self.request.user.get_profile().id]
        )
        return self.guests

Template:

<ul>
    {% for guest in guest_list %}
    <li>{{ guest.first_name }} {{ guest.last_name }}
        <ul>
            <li>Email: {{ guest.email }}</li>
            ...
            <li>Country: {{ guest.country }}</li>
            {% comment %} {{ guest_details.get_country_display }} {% endcomment %}
            <li>Category: {{ guest.guest_category }}</li>
            <li><a href="{% url 'guests:guest_update' pk=guest.pk %}">Edit</a></li>
            <li><a href="{% url 'guests:guest_delete' pk=guest.pk %}">Remove</a></li>
        </ul>
    </li>
    {% endfor %}
</ul>

Basically, users can create guest categories to place their guests in, e.g. Friends, Family. And when creating guests, they may or may not have guest details ready at hand so that is not saved till user creates the guest's details. I tried using prefetch_related on guest_details but if there are no entries, the guest will not be returned.

I want to return rows of information to the user for their guests such that it looks like this:

 first_name | last_name | country | category 
------------+-----------+---------+----------
 Guest      | Two       |     169 | Friends
 Guest      | One       |      13 | Friends
 Guest      | Three     |      10 | Friends
 Guest      | Four      |         | 

Note, guest category is also not a compulsory field. I have used the left outer joins to get the category and details. However, when I load the view in the browser, I noticed that the database is hit for each guest category due to {{ guest.guest_category }}. Could I retrieve the category from guest_category table without resorting to an additional database hit like in the SQL result above?

Is there a better way of achieving what I want? I'm not a stranger to SQL but if there's something more django-ish with caching it would be fantastic.

Answer 1

Actually I realized that prefetch_related works. I made some changes to my models:

class GuestDetails(models.Model):
    guest = models.OneToOneField(Guest, related_name='details')
    ...

Then in views:

guest_list = Guest.objects.filter(profile=profile).prefetch_related('details')

Referencing in templates is easy:

{% for g in guests %}
<li>{{ g.full_name }}</li>
<ul>
    <li>Category: {{ g.category }}</li>
    <li>Email: {{ g.details.email }}</li>
    <li>Country: {{ g.details.get_country_display }}</li>
</ul>
{% endfor %}

However returning g.category in the template would hit the database each time. Need to revisit.