Follow Up: ORM

13 06 2012

My last post was about the good the bad and the ugly of ORM. Having just suprised myself at the ease with which its was possible to build a reasonably complex query in Django ORM, I thought it would be worth sharing by way of an example. The query is a common problem. I want a list of users, and their profile information that are members of a group or subgroups. I have several tables, Profile, Principal, Group, GroupGroups, GroupMembers. The ORM expression is

q = Profile.objects.filter(
     models.Q(user__principal__groupmember__group__name=group)|
     models.Q(user__principal__groupmember__group__parent__group__name=group)
     )

And the resulting SQL, which looks frightening, but appears to scale well on a populated DB is.

SELECT * FROM "user_profile" 
    INNER JOIN "auth_user" 
       ON ("user_profile"."user_id" = "auth_user"."id") 
    INNER JOIN "permission_principal" 
       ON ("auth_user"."id" = "permission_principal"."user_id") 
    INNER JOIN "user_groupmember" 
       ON ("permission_principal"."id" = "user_groupmember"."principal_id") 
    INNER JOIN "auth_group" 
       ON ("user_groupmember"."group_id" = "auth_group"."id") 
    LEFT OUTER JOIN "user_groupgroups" 
       ON ("auth_group"."id" = "user_groupgroups"."child_id") 
    LEFT OUTER JOIN "auth_group" T7 
ON ("user_groupgroups"."group_id" = T7."id") 
    WHERE ("auth_group"."name" = ? OR T7."name" = ? ) 
    ORDER BY "user_profile"."search" ASC LIMIT 25'

Job done, 1 query for the feed paged and sorted, 7ms on a populated DB.

Advertisements

Actions

Information

2 responses

13 06 2012
Charles Severance

Ian – The formatting on this makes it really hard to follow.

14 06 2012
Ian

Yes, sorry, it’s a standard wordpress theme. Not the best for readability or viewing code. I’ll see what I can do.




%d bloggers like this: