On Thu, Aug 24, 2017 at 4:22 PM, Marc Mengel <marc.men...@gmail.com> wrote:
>
> So I had a query in raw PostgreSQL which was trying to find the start and
> end of an event in
> a history table:
>
> select jh.job_id, jh.created as start_t, jh.status, min(jh.created)
> over (PARTITION by jh.job_id
> ORDER BY jh.created
> ROWS BETWEEN 1 FOLLOWING and 3 FOLLOWING
> ) as end_t
> from job_histories jh, jobs j, tasks t
> where jh.status in ('running: copying files in', 'running','Running')
> and jh.job_id = j.job_id
> and j.task_id = t.task_id
> and t.campaign_id = 704
>
> When trying to render this in SQLAlchemy, as in
>
> dbhandle.query(JobHistory.job_id.label('job_id'),
> JobHistory.created.label('start_t'),
> JobHistory.status.label('status'),
>
> func.max(JobHistory.created).over(partition_by = JobHistory.job_id,
>
> order_by=JobHistory.created,
>
> rows=(1,3)
>
> ).label('end_t'))
> .join(Job)
> .join(Task)
>
> .filter(JobHistory.status.in_([copy_start_status,'running','Running']))
> .filter(JobHistory.job_id == Job.job_id)
> .filter(Job.task_id == Task.task_id)
> .filter(Task.campaign_id == campaign_id)
>
>
> I find I cannot ask for "BETWEEN 1 FOLLOWING and 3 FOLLOWING", becuase the
> code insists the first part of
> the range must be none, zero or negative, and the second part must be none,
> zero, or positive. -- thus
> I seem to always have to give a range that spans the current item -- the
> start of the range has to always be before
> the current row (or equal to it) and the end of the range has to always be
> at or after the current row.
>
> Why the sign restrictions?!?
well, I read through
https://www.postgresql.org/docs/9.3/static/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS
over and over and over trying to get my head around some syntax that
could capture everything I could possibly imagine this would do
without too much verbosity, and that's what we came up with.
issue is at
https://bitbucket.org/zzzeek/sqlalchemy/issues/4053/support-range-between-x-following-and-y
and you can try out the patch at
https://gerrit.sqlalchemy.org/changes/487/revisions/fb55270835b18e6ba942b74ee72719ce0fc4d062/archive?format=tgz
please verify it does everything you need thanks!
>
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
