Re: Problem counting names by letter

On Fri, May 24, 2013 at 9:40 PM, Brian Millham <bmillham@gmail.com> wrote:
> 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
>

Manager.raw() is for those use cases where you will be producing a
list of objects to instantiate into objects.

In this case, you aren't producing a list of Artist objects, you are
producing a list of data about artists, and so it makes sense to use
the cursor directly.

Django can help you when you are annotating objects, or aggregating
data about related objects, but for some things, yes, you need to use
SQL.

Cheers

Tom

--
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+unsubscribe@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.