| 1 | from django.db import models
|
|---|
| 2 | from datetime import datetime
|
|---|
| 3 |
|
|---|
| 4 | class Blog(models.Model):
|
|---|
| 5 | name = models.CharField(maxlength=100)
|
|---|
| 6 | tagline = models.TextField()
|
|---|
| 7 |
|
|---|
| 8 | def __str__(self):
|
|---|
| 9 | return "%s (%s)" % (self.name, self.entry_set.count())
|
|---|
| 10 |
|
|---|
| 11 | class Meta:
|
|---|
| 12 | db_table = 'test_blog'
|
|---|
| 13 |
|
|---|
| 14 | class Author(models.Model):
|
|---|
| 15 | name = models.CharField(maxlength=50)
|
|---|
| 16 | email = models.URLField()
|
|---|
| 17 |
|
|---|
| 18 | def __str__(self):
|
|---|
| 19 | return self.name
|
|---|
| 20 |
|
|---|
| 21 | class Meta:
|
|---|
| 22 | db_table = 'test_author'
|
|---|
| 23 |
|
|---|
| 24 | class Entry(models.Model):
|
|---|
| 25 | blog = models.ForeignKey(Blog)
|
|---|
| 26 | headline = models.CharField(maxlength=255)
|
|---|
| 27 | body_text = models.TextField()
|
|---|
| 28 | pub_date = models.DateTimeField(default=datetime.now)
|
|---|
| 29 | authors = models.ManyToManyField(Author)
|
|---|
| 30 |
|
|---|
| 31 | def __str__(self):
|
|---|
| 32 | return self.headline
|
|---|
| 33 |
|
|---|
| 34 | class Meta:
|
|---|
| 35 | db_table = 'test_entry'
|
|---|
| 36 |
|
|---|
| 37 | def test_extra_filter():
|
|---|
| 38 | print "Clearing Blog and Entry rows..."
|
|---|
| 39 | for blog in Blog.objects.all():
|
|---|
| 40 | for entry in blog.entry_set.all():
|
|---|
| 41 | entry.delete()
|
|---|
| 42 | blog.delete()
|
|---|
| 43 | print "Populating database..."
|
|---|
| 44 | mine = Blog(name="My blog", tagline="It's mine")
|
|---|
| 45 | his = Blog(name="His blog", tagline="It's his")
|
|---|
| 46 | hers = Blog(name="Her blog", tagline="It's hers")
|
|---|
| 47 | mine.save(), his.save(), hers.save()
|
|---|
| 48 | Entry(blog=mine, headline="My first").save()
|
|---|
| 49 | Entry(blog=his, headline="His first").save()
|
|---|
| 50 | Entry(blog=his, headline="His second").save()
|
|---|
| 51 | Entry(blog=his, headline="His third").save()
|
|---|
| 52 | Entry(blog=hers, headline="Her first").save()
|
|---|
| 53 | Entry(blog=hers, headline="Her second").save()
|
|---|
| 54 |
|
|---|
| 55 | print "My blog has 1 entry, His blog has 3 entries, and Her blog has 2 entries."
|
|---|
| 56 |
|
|---|
| 57 | print "Now I'll order blogs by how many entries they have..."
|
|---|
| 58 | print "(The expected result is His, Hers, Mine.)"
|
|---|
| 59 |
|
|---|
| 60 | print Blog.objects.extra(select={
|
|---|
| 61 | 'entry_count': """SELECT COUNT(*) FROM test_entry
|
|---|
| 62 | WHERE test_entry.blog_id = test_blog.id"""
|
|---|
| 63 | }).order_by('-entry_count')
|
|---|
| 64 |
|
|---|
| 65 | print "It worked -- clearly 'entry_count' is available in the SQL since ORDER BY saw it."
|
|---|
| 66 |
|
|---|
| 67 | print "Now I'll filter blogs that have 2 entries:"
|
|---|
| 68 | print "(The expected result is Hers.)"
|
|---|
| 69 |
|
|---|
| 70 | try:
|
|---|
| 71 | print Blog.objects.extra(select={
|
|---|
| 72 | 'entry_count': """SELECT COUNT(*) FROM test_entry
|
|---|
| 73 | WHERE test_entry.blog_id = test_blog.id"""
|
|---|
| 74 | }).filter(entry_count=2)
|
|---|
| 75 | except TypeError:
|
|---|
| 76 | print "But it raised an error!"
|
|---|
| 77 | raise
|
|---|
| 78 |
|
|---|