0

I have an app where Posts are split into various categories using the django-categories app, using hard-linking (ForeignKey to categories.Category).

I am also using the django-voting app to allow users to vote_up or vote_down certain posts.

Now I have a view where I require the latest posts from a list of Categories (the users category whitelist) which the user has NOT voted on and they are not his own posts. How do I go about getting these posts in the most efficient manner from the DB query load perspective.

Here is my Post model:

class Post(models.Model):
    published = models.DateTimeField(default=datetime.now)
    author = models.ForeignKey(User, blank=True, null=True,
                               verbose_name='author', 
                               related_name='author_post')
    caption = models.CharField(max_length="240")
    image = models.ImageField(upload_to='user_images')
    up_votes = models.PositiveIntegerField(default=0)
    down_votes = models.PositiveIntegerField(default=0)
    category = models.ForeignKey('categories.Category')

Should I use RAW DB queries to get posts in reverse chronological order where the current logged in user hasn't voted and they aren't his own posts.

Krzysztof Szularz
  • 5,151
  • 24
  • 35
Fahad Yousuf
  • 181
  • 1
  • 10

1 Answers1

1

Depends on your db. If it's PosgtreSQL, you will be fine with subquery. 

voted_already = (Vote.objects.filter(user=…, 
                                     content_type=…)
                             .values_list('object_id', flat=True))
not_voted = (Post.objects.filter(category__in=…)
                         .exclude(author=…, 
                                  pk__in=voted_already)
                         .order_by('-published'))
Krzysztof Szularz
  • 5,151
  • 24
  • 35
  • This looks similar to what I am already doing. I just wasn't quite sure this would scale: `vlist = Vote.objects.filter(user=request.user) post = Post.objects.exclude(author_id=request.user.id, id__in=[item.object_id for item in vlist]).get()` – Fahad Yousuf Feb 05 '13 at 11:26
  • Your solution doesn't use subquery. You pass list of ids. If user has voted on big set of posts the list sent to db would be huge. My solution makes one query which involves subquery. – Krzysztof Szularz Feb 05 '13 at 12:43
  • It should work, but I can not guarantee for subqueries in mysql being quick. – Krzysztof Szularz Feb 05 '13 at 14:19
  • As your solution works (haven't tested impact of load on this) I am marking it as the correct answer. Will update here if I run into trouble as the load increases. – Fahad Yousuf Feb 06 '13 at 06:20