Reducing queries overhead when fetching related fields

Question

I have a simple blog app with tags.

class AbstractDate(models.Model):
     created = models.DateTimeField(auto_now_add=True)

     class Meta:
        abstract = True

class AbstractTitleData(AbstractDate):
    title = models.CharField(max_length=200)

    class Meta:
        abstract = True


class Post(AbstractTitleData):
     body = models.TextField()
     views = models.IntegerField(default=0)
     likes = models.IntegerField(default=0)
     picture = models.ImageField(upload_to='profile_images', blank=True)


    class Meta:
        ordering = ["-created"]

    def __unicode__(self):
        return self.title

class Tag(models.Model):
   slug = models.SlugField(max_length=15)
   post = models.ForeignKey(Post, related_name="tags") 

   def __unicode__(self):
       return self.slug

For example, in my db have two posts. There are post A and post B with tags 'a', 'b', 'c', and 'd', 'e', 'f' respectively. To reduce queries to the database, I'm trying to use extra() method.

condition = 'blog_post.id = blog_tag.post_id'
p = Post.objects.all().extra(select={'t':'blog_tag.slug'},tables=["blog_tag"],where=[condition])

Result:

[<Post: A>, <Post: A>, <Post: A>, <Post: B>, <Post: B>, <Post: B>]
for post in p: print post.t
'a'
'b'
'c'
'd'
'e'
'f'

How can I get a copy of each post with all their tags listed in one attr, like so:

p =[<Post: A>, <Post: B>]
for post in p: print post.t
['a','b','c']
['d','e','f']

Daniel Roseman · Accepted Answer · 2014-04-22 14:13:58Z

1

You wouldn't use extra for that at all. Instead, use prefetch_related to fetch the posts and all their associated tags in two queries:

p = Post.objects.all().prefetch_related('tag')

edited Apr 22, 2014 at 14:13

answered Apr 22, 2014 at 13:55

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A.M. Sultanov Over a year ago

Can i use combination like p = Post.objects.all().prefetch_related('tag').extra(select={'count':"select count(*) from blog_tag,blog_post where blog_post.id=blog_tag.post_id group by blog_post.id"} for additional comments count field

Anto · Accepted Answer · 2014-04-22 14:10:37Z

1

I'm not sure that you want to use a ForeignKey in your Tag model. In Django, a ForeignKey is a many-to-one, while you'd probably prefer a many-to-many relationship (meaning: one post can have multiple tags and one tag can refer to multiple posts).

To deal with performance issues, I'm using select_related:

Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query. This is a performance booster which results in a single more complex query but means later use of foreign-key relationships won’t require database queries.

edited Apr 22, 2014 at 14:10

answered Apr 22, 2014 at 14:02

Anto

7,2629 gold badges47 silver badges67 bronze badges

1 Comment

A.M. Sultanov Over a year ago

Thx, i think about this , but i saw this example in documentation docs.djangoproject.com/en/dev/ref/models/fields/… . But now i shure, that many-to-many is the best approach

AlexJerez · Accepted Answer · 2014-04-22 14:20:16Z

0

Have you tried django-taggit? It may make your life easier. Just plug it in and go. The documentation has some example queries for what you may want to do.

answered Apr 22, 2014 at 14:20

AlexJerez

5004 silver badges5 bronze badges

1 Comment

A.M. Sultanov Over a year ago

Thx, I know about this app, but it is for the understanding of Django and in some test tasks need to use own tags.

Collectives™ on Stack Overflow

Reducing queries overhead when fetching related fields

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related