I tried this
session\
.query(func.count(distinct(build.device_id)))\
.filter(channelbuild.channel_id == 9)\
.filter(build.in_icp == False)\
.scalar()
It generated this sql:
SELECT
count(DISTINCT backfill_build.device_id)
AS
count_1
FROM
backfill_build, backfill_channelbuild
WHERE
backfill_channelbuild.channel_id = %(channel_id_1)s
AND
backfill_build.in_icp = %(in_icp_1)s
-- {'in_icp_1': False, 'channel_id_1': 9}
This worked... however it took 10 minutes...
The query plan for my original sql in postgres looks like this:
seq_scan: channelbuild -----
Hash join --> aggregate
seq_scan: build ---> Hash --
And in the generated sql:
seq_scan: build -------------------------
Nested loop --> aggregate
seq_scan: channelbuild --> Materialize --
Having some trouble getting it working with the join in there.
For instance I tried this:
unmapped_builds = channelbuild.join((build, build.id ==
channelbuild.build_id))\
.filter(channelbuild.channel_id == 9)\
.filter(build.in_icp == False)
However device_id is not available:
>>> unmapped_builds.first()
MappedBackfill_channelbuild(id=60805,channel_id=9,build_id=221849)
Any clues on how I can get the query working using count and distinct
together ?
On Apr 1, 3:38 pm, "Michael Bayer" <[email protected]> wrote:
> Stu.Axon wrote:
>
> > I'm having another go at converting some sql to sqlalchemy:
>
> > select
> > count(distinct build.device_id)
> > from
> > channelbuild
> > join
> > build
> > on
> > build.id = channelbuild.build_id
> > where
> > channelbuild.channel_id = 9
> > and not
> > build.in_icp"""
>
> > I've got this far:
>
> > build = db.build
> > channelbuild = db.channelbuild
>
> > channelbuild\
> > .join((build, build.id == channelbuild.build_id))\
> > .filter(channelbuild.channel_id == 9)\
> > .filter(build.in_icp == False)\
> > .count()
>
> try query(func.count(distinct(build.device_id))).filter(...).scalar()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
