from django.db import models
from datetime import datetime

class Blog(models.Model):
    name = models.CharField(maxlength=100)
    tagline = models.TextField()

    def __str__(self):
        return "%s (%s)" % (self.name, self.entry_set.count())

    class Meta:
        db_table = 'test_blog'

class Author(models.Model):
    name = models.CharField(maxlength=50)
    email = models.URLField()

    def __str__(self):
        return self.name
    
    class Meta:
        db_table = 'test_author'

class Entry(models.Model):
    blog = models.ForeignKey(Blog)
    headline = models.CharField(maxlength=255)
    body_text = models.TextField()
    pub_date = models.DateTimeField(default=datetime.now)
    authors = models.ManyToManyField(Author)

    def __str__(self):
        return self.headline
    
    class Meta:
        db_table = 'test_entry'

def test_extra_filter():
    print "Clearing Blog and Entry rows..."
    for blog in Blog.objects.all():
        for entry in blog.entry_set.all():
            entry.delete()
        blog.delete()
    print "Populating database..."
    mine = Blog(name="My blog", tagline="It's mine")
    his = Blog(name="His blog", tagline="It's his")
    hers = Blog(name="Her blog", tagline="It's hers")
    mine.save(), his.save(), hers.save()
    Entry(blog=mine, headline="My first").save()
    Entry(blog=his, headline="His first").save()
    Entry(blog=his, headline="His second").save()
    Entry(blog=his, headline="His third").save()
    Entry(blog=hers, headline="Her first").save()
    Entry(blog=hers, headline="Her second").save()
    
    print "My blog has 1 entry, His blog has 3 entries, and Her blog has 2 entries."
    
    print "Now I'll order blogs by how many entries they have..."
    print "(The expected result is His, Hers, Mine.)"
    
    print Blog.objects.extra(select={
        'entry_count': """SELECT COUNT(*) FROM test_entry
                          WHERE test_entry.blog_id = test_blog.id"""
        }).order_by('-entry_count')
    
    print "It worked -- clearly 'entry_count' is available in the SQL since ORDER BY saw it."
    
    print "Now I'll filter blogs that have 2 entries:"
    print "(The expected result is Hers.)"
    
    try:
        print Blog.objects.extra(select={
            'entry_count': """SELECT COUNT(*) FROM test_entry
                              WHERE test_entry.blog_id = test_blog.id"""
            }).filter(entry_count=2)
    except TypeError:
        print "But it raised an error!"
        raise