Mr. Z wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
