CODEWITHSUNDEEP
djangoormleft-join
Alb
Alb

Reputation: 1125

Left Join in Django ORM

Say I have models called "Post" and "Reaction" and I have made a Post, saved it and now I want to react on it with a Reaction. Now, when I visit my feed where a list of Posts is, I want to show my reaction if I had one.

Which means: {% if post.my_reaction %}{{ post.my_reaction.contents }}{% endif %} Now, obviously I want to load more than just one Post; posts which don't have my reactions on it.

I know that I used to solve this with SQL in PHP with

SELECT * FROM Post 
LEFT JOIN Reaction ON Reaction.Post = Post.id AND Reaction.Author = <me>

My models:

class Post(models.Model):
    content = RichTextField()
    author = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    datetime = models.DateTimeField(auto_add_now=True)

class Reaction(models.Model):
    reaction = models.CharField(max_length=1)
    post = models.ForeignKey(Post, on_delete=models.CASCADE, related_name="reactions")
    author = models.ForeignKey(User, on_delete=models.CASCADE)

It's not quite clear how I could achieve this in Django

Upvotes: 1

Views: 416

Answers (2)

2ps
2ps

Reputation: 15926

Just drop into raw:

results = Post.objects.raw("""
SELECT * FROM Post 
LEFT outer JOIN Reaction ON Reaction.Post = Post.id AND Reaction.Author = %s
""", [ '<me>', ])

Upvotes: 1

ChidG
ChidG

Reputation: 3223

You could achieve this by creating a custom template tag to query the posts for the logged in user. Although it's optional, to me a good place for that query to live would be on the Post model.

class Post(models.Model):
    content = RichTextField()
    author = models.ForeignKey(User, on_delete=models.SET_NULL, null=True)
    datetime = models.DateTimeField(auto_add_now=True)

   def user_reactions(self, user):
        return self.reactions.filter(author=user)

You would then need to create a custom template tag to call that method on the post object while passing in the user from the request.

from django import template

register = template.Library()


@register.simple_tag # (Django >= 1.9, earlier versions need to use `assignment_tag`)
def post_user_reactions(post, user):
    return post.user_reactions(user)

This would return a queryset of reactions for that user, which you can then iterate through in the template:

{% post_user_reactions post user as user_reactions %}
{% for reaction in user_reactions %}
  Do stuff with reaction object
{% endfor %}

Please keep in mind that making database queries in a for-loop (as you probably are, say if you're displaying a list of posts in this case), can end up being very expensive. You'll want to add a prefetch_related or a queryset annotation to do this.

As some advice, the Django ORM is a slightly different way of thinking about database access than traditional SQL. Thinking about 'left joins' won't help - you need to think in terms of Django objects and their relationships.

Upvotes: 1

Related Questions