What's the "Django Way" to Do This Query?

Question

I have a database that has users, pictures, and comments (users can comment on pictures). I'm trying to query the database for the first picture that the currently logged in user has not commented on. I have it working in raw SQL, but would prefer to do it properly.

How can I convert my raw SQL into regular Django code?

Relevant code

models.py

from django.contrib.auth.models import User
from django.db import models


class Comment(models.Model):
    user = models.ForeignKey(User)
    picture = models.ForeignKey('Picture')
    body = models.TextField()

    def __unicode__(self):
        return u'%s: %s' % (self.user.get_full_name(), self.picture.title)


class Picture(models.Model):
    title = models.CharField(max_length=100)
    order = models.PositiveIntegerField()

    class Meta:
        ordering = ('order',)

    def __unicode__(self):
        return self.title

urls.py

from django.conf.urls import patterns, include, url
from django.contrib import admin
admin.autodiscover()

from core import views

urlpatterns = patterns('',
    url(r'^$', views.Home.as_view(), name='home'),
    url(r'^admin/', include(admin.site.urls)),
)

views.py

from django.views.generic.base import TemplateView
from .models import Picture


class Home(TemplateView):
    template_name = 'core/first_picture_without_comment.html'

    def get_context_data(self, **kwargs):
        sql = '''
            SELECT `core_picture`.`id`, COUNT(`comments_made`.`id`) AS `count`
            FROM `core_picture`
            LEFT OUTER JOIN (
                SELECT `core_comment`.`id`, `core_comment`.`picture_id`
                FROM `core_comment`
                WHERE `core_comment`.`user_id` = %s
            ) AS `comments_made` ON (`core_picture`.`id` = `comments_made`.`picture_id`)
            GROUP BY `core_picture`.`id`
            HAVING `count` = 0
            ORDER BY `core_picture`.`order`
            LIMIT 1
        '''

        sql_params = [
            self.request.user.pk,
        ]

        picture = Picture.objects.raw(sql, sql_params)[0]

        return {
            'picture': picture,
        }

first_picture_without_comment.html

<h1>The First Picture You Have Not Commented On</h1>

<p>{{ picture.title }}</p>

How to test

Name the test app "core". Run syncdb (be sure to create a superuser!) and then insert this data into the database:

INSERT INTO `auth_user` VALUES(2, 'john_doe', 'John', 'Doe', 'john_doe@example.com', '', 1, 1, 0, '2013-06-11 02:23:23', '2013-06-11 02:01:07');
INSERT INTO `auth_user` VALUES(3, 'jane_doe', 'Jane', 'Doe', 'jane_doe@example.com', '', 1, 1, 0, '2013-06-11 02:01:21', '2013-06-11 02:01:21');

INSERT INTO `core_picture` VALUES(1, 'Foo', 4);
INSERT INTO `core_picture` VALUES(2, 'Bar', 3);
INSERT INTO `core_picture` VALUES(3, 'Baz', 2);
INSERT INTO `core_picture` VALUES(4, 'Qux', 1);

INSERT INTO `core_comment` VALUES(1, 2, 1, 'This picture is great!');
INSERT INTO `core_comment` VALUES(2, 2, 4, 'I like this picture!');
INSERT INTO `core_comment` VALUES(3, 3, 4, 'I like this picture too!');
INSERT INTO `core_comment` VALUES(4, 2, 4, 'I like it more!');
INSERT INTO `core_comment` VALUES(5, 3, 2, 'This picture is awesome!');
INSERT INTO `core_comment` VALUES(6, 3, 3, 'I love this!');

Go into Django admin and set a password for both of the regular users. After that, you can log in with each one to test it out. John Doe's should say "Baz" and Jane Doe's should say "Foo". This is correct; I just want to know how to do this without using raw SQL.

Answer 1

You can do this:

first_picture_without_comment = Picture.objects.exclude(id__in= Comment.objects.filter(user= request.user).values_list('picture__id', flat=True)).order_by('id')[0]

To simplify:

pic_ids_with_comments = Comment.objects.filter(user= request.user).values_list('picture__id', flat=True)
first_picture_without_comment_qs = Picture.objects.exclude(id__in= pic_ids_with_comments).order_by('id')
if first_picture_without_comment_qs.exists(): 
    first_pic = first_picture_without_comment_qs[0]

This gives the first picture created (based on id), which is not commented by this user