Django ORM inner join query on 2 tables that share same FK to User model

Using Django Using the ORM
1

hi all, question,

I have the built in User model

and 2 more models

Application:
  user = OneToOneField(User, etc)
  ..additional fields ...

ImageModel:
  user = OneToOneField(User, etc)
  .. additional fields ..

Im trying to get a queryset from both Application and Image models that contains all fields from both tables, where Application.user_id = ImageModel.user_id.all()

I cant figure out to build ORM statement that will do this raw sql (which works)

        q = """
        SELECT *
        FROM main_application
        INNER JOIN main_imagemodel ON main_application.user_id = main_imagemodel.user_id
        """

I tried select_related but keep getting errors

result = Application.objects.select_related(“imagemodel”).all()

Invalid field name(s) given in select_related: 'imagemodel'. Choices are: user,

2

I don’t think you can do this with the ORM since the returned data probably won’t match the Model you’re doing the queries.
Either way, we can’t help without the complete models related to this question.

3

This is one of those situations where you want to start to switch your brain from a “relational SQL” mindset to an “ORM mindset”.

First, to make something clear - “select_related” is not a requirement for accessing related models. It’s a (very useful) performance improvement. As a result, using (or not using) it doesn’t materially affect how the relationships are used.

Since these are all one-to-one relationships, no “query” is necessary. Access is “direct” through the OneToOneField.

These two tables are linked through the User model, so you have at least three different ways to do this.

Now, not assuming anything not directly shown:

  • User.objects.all() is the set of all User. If you have an instance of User named user, then user.application is the related Application and user.imagemodel is the related ImageModel. You can then optimize these references by writing User.objects.all().select_related('application', 'imagemodel')

  • Application.objects.all() gives you the queryset of all Application. If you have an instance of Application named application, then application.user is the related User object, making application.user.imagemodel the ImageModel related to that User. This can be optimized by using Application.objects.all().select_related('user__imagemodel')

  • Finally, you can do this starting from ImageModel.objects.all() (basically the mirror image of the prior case).

You may also want to review the docs and examples at Model field reference | Django documentation | Django and One-to-one relationships | Django documentation | Django

3 Likes
4

this is excellent explanation thank you

I had to modify the models a bit and give the OneToOne unique related_name, like this,

Application model


class Application(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE, related_name="userapp", db_index=True, primary_key=True)
   dob = models.DateField(blank=True, null=True)
   age = models.IntegerField(blank=True, null=True)

ImageModel


class ImageModel(models.Model):
    """ save head, body image to media folder, generate a thumbnail img from head img """
    def wrapper(instance, filename, target):
        ext = filename.split(".")[-1].lower()

        if ext not in ["jpg", "png", "gif", "jpeg"]:
            raise ValidationError(f"invalid image extension: {filename}")

        if ext == "jpeg":
            ext = "jpg"

        if instance.pk:
            filename = os.path.join("images", f"{instance.pk}_{target}.{ext}".lower())
        else:
            filename = os.path.join("images", f"{filename}_{target}.{ext}".lower())

        if os.path.lexists(f"{settings.MEDIA_ROOT}/{filename}"):
            os.remove(f"{settings.MEDIA_ROOT}/{filename}")

        return filename

    user = models.OneToOneField(User, on_delete=models.CASCADE, related_name="userimg", db_index=True, primary_key=True)
    head_img = models.ImageField(null=True, blank=True, upload_to=partial(wrapper, target="head"))
    body_img = models.ImageField(null=True, blank=True, upload_to=partial(wrapper, target="body"))
    thumb_img = models.ImageField(null=True, blank=True) # same as head_img but resized and shrunk on quality

and then in my view I can get data from all 3 tables


def search(request):
    if request.method == "POST":
        # query both Application and Image models
        queryset = Application.objects.all()
        context = {"search_form": ApplicantSearchForm, "search_data": queryset, "media": settings.MEDIA_URL}
    else:
        context = {"search_form": ApplicantSearchForm}
    return render(request, "search.html", context=context)

and in my template, I see the image paths coming from the ImageModel table,

search.html


{% for d in search_data %}
   {{ d.age }}  << Application data
   {{ d.user.last_login }} << User data
   {{ d.user.userimg.head_img }} << ImageModel data
{% endfor %}

much cleaner than raw sql, thank you

5

Now it’s a good time to add the select_related, because the current implementation is going to issue 2 additional queries for each “Application” object you have on the database.
With select_related you’re going to do all on one query.

Application.objects.select_related("user", "user__userimg").all()
1 Like
6

thank you added this, its working