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 at
> http://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.
