I may be showing my ignorance of Django and python here but:
in MySQL there is an aggregate function GROUP_CONCAT that will do
precisely what you want. There is a PostgreSQL library that adds the
functionality as well.
I'm still getting used to the ORM is Django, so I'm not sure how you
would add that in, but I'm sure there is a way. Otherwise raw SQL
works, too.
Corey
On Aug 4, 2006, at 5:07 PM, gabor wrote:
>
> hi,
>
> i have a problem with a certain web-application, and wonder if someone
> has an idea how to solve it the best way....
>
> imagine the following situation..
>
>
> class Role(Model):
> title = CharField(maxlength=100)
>
> class User(Model):
> name = CharField(maxlength=100)
> roles = ManyToManyField(Role)
>
>
> now, i would like to list all the Users and their details on a page,
> in a html-table, and in the column for the "roles" i would like all
> the
> roles's titles as comma-separated text.
>
> for example:
>
> Joe | pawn,bishop |
> Bill | knight,rook,pawn |
> Jane | queen |
>
>
> as i assume this is a fairly common situation, how would you do it?
>
> of course there is the most straightforward version, where you simply
> ask the user instance for it's all roles, and join them by ",".
>
> but this has the problem that if you have 100 users, then you will
> make
> 101 queries (1 to list all the users, and one for each user to
> query the
> roles).
>
> is there a faster/better way?
>
> there are 2 ways i have thought about:
>
> 1. execute a "raw" sql query which simply selects all the roles for
> all
> the users. then manually build a dictionary from this data (the key
> being User.id, and the value is the comma-separated list). and then
> access this data in the html page.
>
> 2. slightly faster: define an aggregate function for concatenating
> text
> in the database, and do a "raw" sql query which returns a much better
> data structure (a list of (user_id,text) pairs), and then access this
> data in the html page.
>
> are there any better ways?
>
> my biggest problem with these approaches is:
>
> a. i cannot use the generic view's pagination anymore, because i need
> all the user_ids before i call the generic view
>
> b. it's complicated to access such data-structure in the html page.
> the
> best way i came up is to have a custom page-template that gets the
> data
> from the dictionary. at first it seems simple, but then you realize
> that
> if you have a dictionary where user_id => text, then it's not possible
> (afaik) to ask in the page template for certain value in the
> dictionary.
> means i cannot do {{ user_roles.{{ user.id }} }} . or can i?
>
> as i said, this seems to be a common situation. how do you approach
> this
> problem?
>
> gabor
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users
-~----------~----~----~----~------~----~------~--~---
