Thanks for reply. I finally got the right result as following:
for article in session.query(Article, func.count(Article.id).label
('count')) \
.join(Article.keywords).group_by(Article.name) \
.filter(Keyword.name.in_(['k2', 'k3', 'k6',
'k7'])).order_by('count DESC'):
print article
And the result looks like this:
(<article_2>, 3)
(<article_1>, 2)
(<article_3>, 2)
(<article_4>, 1)
However, is it possible to further filter the labeled `count` to be
>=2? So the result would become:
(<article_2>, 3)
(<article_1>, 2)
(<article_3>, 2)
Thanks.
On Dec 23, 6:09 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> On Dec 22, 2009, at 1:39 AM, Olli Wang wrote:
> > Hi, is it possible to query data from a relational table according to
> > a list of matched conditions? For example, assume I have two tables,
> > Article and Keywords, in a Many-to-Many relationship, and have data
> > like this:
>
> > Article 1 has keywords (k1, k2, k3, k4)
> > Article 2 has keywords (k2, k3, k5, k6)
> > Article 3 has keywords (k1, k3, k4, k5, k6)
> > Article 4 has keywords k1, k2, k4, k5)
>
> > Now I want to find all articles in the order of the number of matched
> > keywords (k2, k3, k6, k7), and the result should like this:
>
> > 1) Article 2, because it has 3 matched keywords: (k2, k3, k6)
> > 2) Article 1, because it has 2 matched keywords: (k2, k3)
> > 3) Article 3, because it has 2 matched keywords: (k3, k6)
> > 4) Article 4, because it has only 1 matched keyword: (k2)
>
> > Note that because no article has the keyword "k7", so it just not
> > count. Please help. Thanks.
>
> you'd likely want to join from Article to Keywords along the many-to-many
> table, and filter for those Keywords which identify as k2, k3, k6, k7, likely
> using an "in". This join woud look along the lines of:
>
> session.query(Article).join(Article.keywords).filter(Keyword.name.in_('k2',
> 'k3', 'k6', 'k7'))
>
>
>
>
>
> > --
>
> > You received this message because you are subscribed to the Google Groups
> > "sqlalchemy" group.
> > To post to this group, send email to sqlalch...@googlegroups.com.
> > To unsubscribe from this group, send email to
> > sqlalchemy+unsubscr...@googlegroups.com.
> > For more options, visit this group
> > athttp://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
