python - many to many django sql -

i have model userprofile manytomany relationship table called skill have model group manytomany relationship table called skill

i make query count me number of skills userprofile have in common each group.

i make query count me numbers of skills specific userprofile has in cummon others user profile

could me this. thank you

the models :

class userprofile(models.model):         slug = models.slugfield(max_length=200)     user = models.foreignkey(user, unique =true)     skills = models.manytomanyfield(skills,null=true, blank=true)     courses = models.manytomanyfield(course,null=true, blank=true)  class group(models.model):     slug = models.slugfield(max_length=200)     name = models.charfield(max_length=200)     skills = models.manytomanyfield(skills,null=true, blank=true)  class skills(models.model):     slug = models.slugfield(max_length=200)     name = models.charfield(max_length=200) 

so found way groups

grouprecommendation = group.objects.extra( select={     'skills_count': """      select count(*) axiom_alto_userprofile_skills      join axiom_alto_group_skills on axiom_alto_userprofile_skills.skills_id = axiom_alto_group_skills.skills_id      axiom_alto_group_skills.group_id= axiom_alto_group.id , axiom_alto_userprofile_skills.userprofile_id = %d """ % profile.id,  },  ).order_by('-skills_count') 

but don't know how between users

your first query count, specific user profile, number of skills user shares each group. in post, show way using subquery. however, in databases subqueries have poorer performance joins, might interested see how same results using join instead of subquery:

select g.id, g.slug, g.name, count(gs.id) skills_count axiom_alto_group g inner join axiom_alto_userprofile_skills   on us.userprofile_id = %s left outer join axiom_alto_group_skills gs   on gs.group_id = g.id , us.skills_id = gs.skills_id group g.id, g.slug, g.name order skills_count desc 

in django can run using raw sql query on group model:

group.objects.raw(''' ... sql above ... ''', [profile.id]) 

it should easy see how perform second query. is, count, specific user profile, number of skills user shares each other user:

select u.id, u.slug, u.user, count(us2.id) skills_count axiom_alto_userprofile u inner join axiom_alto_userprofile_skills us1   on us1.userprofile_id = %s left outer join axiom_alto_userprofile_skills us2   on us2.userprofile_id = u.id , us2.skills_id = us1.id group u.id, u.slug, u.user order skills_count desc 

again, in django can run using raw sql query, time on userprofile model:

userprofile.objects.raw(''' ... sql above ... ''', [profile.id])