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.
