Something like this should work:
select(['count(distinct flow.node_id) as nodes', ip],
from_obj=[flow.join(id)], group_by=[flow.c.src_id], having=['nodes >
5'], order_by=[desc('nodes')])
If you wanted to do it w/o text blocks I think you'd have to create a
subselect first to pull the nodes "column" off of.
On 2/23/07, Arthur Clune <[EMAIL PROTECTED]> wrote:
>
>
> This should be easy, but I'm having real problems with it. I'd like
> to do the following SQL (this is from MySQL) as a sqa query:
>
> select count(distinct flow.node_id) as nodes, ip.ip_addr,
> ip.country, ip.domain, ip.isp, ip.city from flow, ip where flow.src_id =
> ip.id group by flow.src_id having nodes > 5 order by nodes desc;
>
> I can't work out how to do the 'having nodes > 5' bit at all. All
> help gratefully received!
>
> For info, the tables look like this (with some fields missed out for
> clarity):
>
> Tables are pretty straightfoward. Slighty edited, they look like this:
>
> owner_table = Table("node",
> Column("id", Integer, primary_key=True),
> [more stuff]
> )
>
> # location data based on the fields from ip2location
> ip_table = Table("ip",
> Column('id', Integer, primary_key=True),
> Column('ip_addr', String(16), nullable=False, index=True,
> unique=True),
> Column('isp', Unicode, default=0.0),
> Column('domain', Unicode, default=0.0),
> Column('country', Unicode, default=None),
> Column('city', Unicode, default=None),
> )
>
> flow_table = Table("flow",
> Column("id", Integer, primary_key=True),
> Column("owner_id", Integer, ForeignKey("honeypot.id"),
> nullable=False),
> Column("ip_proto", Integer, default=6, nullable=False),
> Column("src_id", Integer, ForeignKey("ip.id"), nullable=False),
> Column("dst_id", Integer, ForeignKey("ip.id"), nullable=False),
> )
>
> Then I define the obvious mappers.
>
> Cheers,
>
> Arthur
>
> --
> Arthur Clune
>
>
>
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
