Jonathan,
Thank you! I've tried the code you posted and it works like a charm.
It really has helped me to understand how to do it. I have modified it
to include also a calculation for how many teas have in common Peter
and the people with at least one of the same teas as Peter. This way I
can then apply some "weight" to the recommendations, so depending on
how "similar" peter's and person X's tastes are (more/less teas in
common), the concrete recommendation of person X is more or less
important and I can add a higher or lower value to that person's
recommendations.
This is the code right now:
====================
peter = Person.objects.get(name='Peter')
peter_likes_ids = peter.likes.values_list('id', flat=True)
# People who like at least one of the same teas as Peter, and how
much of teas in common do they have (like_same_count)
similarities =
Person.objects.filter(likes__in=peter_likes_ids).exclude(id=peter.id).extra(
select={
'like_same_count': 'COUNT(%s.kindoftea_id)' %
Person._meta.get_field('likes').m2m_db_table(),
},
order_by=['id', 'name']
)
people_table = Person._meta.db_table
similarities.query.group_by.extend(['%s.id' % people_table,
'%s.name' % people_table])
similarities.values_list('id','name','like_same_count')
# just the ids of the people who like at least one tea of the same
teas as Peter
people = similarities.values_list('id', flat=True)
# Teas liked by the above people, excluding the teas Peter likes
recommendations = KindOfTea.objects.filter(
liked_by__in=people).exclude(id__in=peter_likes_ids).extra(
select={
'like_count': 'COUNT(%s.kindoftea_id)' %
Person._meta.get_field('likes').m2m_db_table(),
},
order_by=['-like_count', 'name']
)
tea_table = KindOfTea._meta.db_table
recommendations.query.group_by.extend(['%s.id' % tea_table,
'%s.name' % tea_table])
# 3-tuples of (tea id, tea name, relevant people who like it
count)
recommendations.values_list('id', 'name', 'like_count')
I have tried to order similarities by '-like_same_count' but it always
gives an error: "Cannot resolve keyword 'like_same_count' into field.
Choices are: id, likes, name" . I don't know why this is happening
because I have set like_same_count as an extra parameter in the select
statement for similarities.
I'll try to add the weight calculation and post the solution here.
Regards.
On 10 nov, 02:43, Jonathan Buchanan <[EMAIL PROTECTED]>
wrote:
> Mr.Zwrote:
> > I have these models:
>
> > KindOfTea(models.Model):
> > name = models.CharField(max_length=100, unique=True)
> > description = models.TextField()
>
> > Person(models.Model):
> > name = models.CharField(max_length=100, unique=True)
> > likes = models.ManyToManyField(KindOfTea, related_name='liked_by')
>
> > I have 1 Person object and some related KindOfTea objects he likes.
> > With this information I want to know which other new kinds of tea he
> > would like. What I want to do is to get the other Person objects that
> > like the same kinds of tea as this person and obtain all the other
> > kinds of tea they like ordered by how many of them like the same one.
>
> > For example:
> > =========
> > Peter likes black tea and white tea
> > Laura likes black tea, green tea and red tea
> > Paul likes black tea, green tea and red tea
> > John likes black tea, green tea and yellow tea
>
> > To recommend Peter a new kind of tea I get the other persons that like
> > "black tea" and/or "white tea": Laura, Paul and John. Then I get
> > somehow the kinds of tea they like that are different than 'black tea'
> > and 'white tea' (the kinds of tea Peter likes) and for them I store
> > how many of those persons like them. So somehow I get a dictionary
> > that looks like this:
> > other_people_like = { 'green tea' : 3, 'red tea' : 2, 'yellow tea':
> > 1 } And I can recommend Peter those kinds of tea in that order.
>
> > I have started with this:
> > =================
> > from sets import Set
> > p = Person.objects.get(name='Peter')
>
> > other_people = Set([])
>
> > for tea in p.likes.all():
> > for person in tea.liked_by.exclude(name='Peter')
> > other_people.add(person)
>
> > # now I have a Set with the people that like some or at least one kind
> > of tea Peter likes too. now how should I do the rest?
>
> > Thank you.
>
> Here's one approach which seems to work for the sample data and expected
> output you provided (tried it in SQLite, MySQL 4.1 and PostgreSQL 8.1):
>
> peter = Person.objects.get(name='Peter')
> peter_likes_ids = peter.likes.values_list('id', flat=True)
>
> # People who like at least one of the same teas as Peter
> people = Person.objects.filter(
> likes__in=peter_likes_ids).exclude(id=peter.id).values('id')
>
> # Teas liked by the above people, excluding the teas Peter likes
> recommendations = KindOfTea.objects.filter(
> liked_by__in=people.query).exclude(id__in=peter_likes_ids).extra(
> select={
> 'like_count': 'COUNT(%s.kindoftea_id)' % \
> Person._meta.get_field('likes').m2m_db_table(),
> },
> order_by=['-like_count', 'name']
> )
> tea_table = KindOfTea._meta.db_table
> recommendations.query.group_by.extend(['%s.id' % tea_table,
> '%s.name' % tea_table])
>
> # 3-tuples of (tea id, tea name, relevant people who like it count)
> recommendations.values_list('id', 'name', 'like_count')
>
> Note that the use of group_by to insert grouping criteria isn't yet part
> of the public API [1]. The resulting recommendations QuerySet will
> generate a query something like the following (this is some initial
> target SQL I wrote to work against, not exactly what the Django ORM will
> produce):
>
> SELECT
> app_kindoftea.id,
> app_kindoftea.name,
> COUNT(app_person_likes.kindoftea_id) AS like_count
> FROM
> app_person_likes
> INNER JOIN app_kindoftea
> ON app_person_likes.kindoftea_id = app_kindoftea.id
> WHERE app_person_likes.person_id IN
> (
> SELECT
> app_person_likes.person_id
> FROM
> app_person_likes
> INNER JOIN app_kindoftea
> ON app_person_likes.kindoftea_id = app_kindoftea.id
> WHERE app_kindoftea.id IN (1, 2)
> AND app_person_likes.person_id != 1
> )
> AND app_kindoftea.id NOT IN (1, 2)
> GROUP BY app_kindoftea.id, app_kindoftea.name
> ORDER BY like_count DESC, app_kindoftea.name ASC
>
> Regards,
> Jonathan.
>
> [1]http://www.eflorenzano.com/blog/post/secrets-django-orm/
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
