Gunnlaugur Briem wrote:
> You can put the func.count() label in a variable and filter on it:
>
> articlecount = func.count(Article.id).label('count')
> for article in session.query(Article, articlecount) \
> .join(Article.keywords).group_by(Article.name) \
> .filter(Keyword.name.in_(['k2', 'k3', 'k6', 'k7'])) \
> .filter(articlecount >= 2) \
> .order_by(articlecount.desc()):
>
> (Untested, but I believe that's about right)
you need to use having() and not filter() to filter aggregates.
>
> - G.
>
>
>
> On Dec 23, 7:03 am, Olli Wang <olliw...@ollix.com> wrote:
>> 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.
>
>
>
--
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.
