Hi,
I have the model described at
http://docs.djangoproject.com/en/dev/topics/db/models/#intermediary-manytomany
on which I have multiple rows in Membership with the same Person and
Group (say they're a bit flaky, and leave and rejoin a few times ;) ). I
wanted to print out a paginated list of groups someone is in, with all
their joining dates in each group result.
I decided to try the new aggregate functionality. Here's my view:
from aggregates import Concatenate
groups = person.group_set
.annotate(Concatenate('membership__date_joined'))
.order_by('name')
page = Paginator(groups, 10).page(1)
And my Concatenate class looks like this:
from django.db.models import Aggregate
from django.db.models.sql.aggregates import Aggregate as AggregateSQL
from django.db.models import DecimalField
class ConcatenateSQL(AggregateSQL):
sql_function = 'GROUP_CONCAT'
def __init__(self, col, separator='|', source=None, **extra):
self.sql_template = "%%(function)s(%%(field)s ORDER BY
%%(field)s SEPARATOR '%s')" % separator
c = DecimalField() # XXX
super(ConcatenateSQL, self).__init__(col, source=c, **extra)
class Concatenate(Aggregate):
name = 'Concatenate'
def add_to_query(self, query, alias, col, source, is_summary):
aggregate = ConcatenateSQL(col, separator=' / ',
is_summary=is_summary)
query.connection.ops.check_aggregate_support(aggregate)
query.aggregates[alias] = aggregate
This works lovely, so the only issue I found was that I had to use a
fake DecimalField() in order for the result from the database to get
past the call to convert_values() in django/db/backends/__init__.py
(called from django/db/models/sql/query.py in resolve_aggregate()). This
function appears to only want numbers/datetimes to go in, and in this
case I'm obviously returning text. Not sure what to suggest as a
solution, as there are presumably other things going on of which I'm not
aware, but the above works for me :)
ATB,
Matthew
P.S. If anyone's interested, prior to aggregate support, I was doing
something like this to get the joining dates per group:
groups = person.group_set.all().distinct().order_by('name')
page = Paginator(groups, 10).page(1)
ids = [ group.id for group in page.object_list ]
members = Membership.objects.filter(group__in=ids, person=person)
dates_joined = {}
for m in members:
dates_joined.setdefault(m.group, []).append(m.date_joined)
for group in page.object_list:
group.dates_joined = dates_joined[group]
Which worked fine, but I felt was a bit fiddly.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
