CODEWITHSUNDEEP
pythonpeewee
Konrad Wąsowicz
Konrad Wąsowicz

Reputation: 432

Peewee ORM get similar entries based on foreign key

I'm having problem with writing query for getting similar posts in a blog based on tags they have. I have following models:

class Articles(BaseModel):
    name = CharField()
      ...

class Tags(BaseModel):
    name = CharField()

class ArticleTags(BaseModel):
    article = ForeignKeyField(Articles, related_name = "articles")
    tags    = ForeignKeyField(Tags, related_name = "tags")

What i'd like to do is to get articles with similar tags sorted by amount of common tags.

Edit

After 2 hours of fiddling with it i got the anwser i was looking for, i'm not sure if it's the most efficient way but it's working:

Here is the function if anyone might need that in the future:

 def get_similar_articles(self,common_tags = 1, limit = 3):
        """
        Get 3 similar articles based on tag used
        Minimum 1 common tags i required
        """
        art = (ArticleTags.select(ArticleTags.tag)\
               .join(Articles)\
               .where(ArticleTags.article == self))

        return Articles.select(Articles, ArticleTags)\
               .join(ArticleTags)\
               .where((ArticleTags.article != self) & (ArticleTags.tag << art))\
               .group_by(Articles)\
               .having(fn.Count(ArticleTags.id) >= common_tags)\
               .order_by(fn.Count(Articles.id).desc())\
               .limit(limit)

Upvotes: 0

Views: 109

Answers (1)

coleifer
coleifer

Reputation: 26225

Just a stylistic nit, table names (and model classes) should preferably be singular.

# Articles tagged with 'tag1'
Articles.select().join(ArticleTags).join(Tags).where(Tags.name == 'tag1')

Upvotes: 1

Related Questions