With this simple database table:
class Artist(models.Model):
id = models.IntegerField(primary_key=True)
name = models.CharField(max_length=255L, blank=True)
prefix = models.CharField(max_length=32L, blank=True)
class Meta:
db_table = 'artist'
I want to be able to get a list of the first letter of the names with a
count, for example with these artists in the table:
ABBA, America, Asia, Blondie, Billy Joel, Charo
I want this result
letter=A lcount=3
letter=B lcount=2
letter=C lcount=1
To complicate things, the database is not the default database.
I've tried this raw query
letters = Artist.objects.using('database_name').raw('SELECT id,
UPPER(LEFT(name,1)) AS letter, COUNT(DISTINCT name) AS lcount FROM
artist GROUP BY UPPER(LEFT(name,1)) ORDER BY UPPER(name)')
but I get this error: AttributeError: 'QuerySet' object has no attribute
'raw'
I've been able to do it using a cursor directly. Is that the only way?
Or am I missing something. I'm trying to limit my use of raw and direct
queries.
I'd like to be able to do something like this:
Artist.objects.using('database_name').aggregate(lc=
Count('upper(left(name,1))'))
but that doesn't work: FieldError: Cannot resolve keyword
'upper(left(name,1))' into field. Choices are: id, name, prefix
Any ideas?
Thanks!
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
