I should have mentioned that - cutoff is simply a float, e.g.
query = query.where(query.c.brayCurtis >= 0.8)
that's why I think there is a trivial solution. If I do the above, the
whole query will be added as a subquery and the where and order by
clauses duplicated. It works fine for the order_by statement though.
Is there a recommended strategy for adding (where) clauses to
calculated columns? Actually what I do is that:
abs(ar1-ar2).sum() / float( abs(ar1+ar2).sum() ) # NumPy notation
the corresponding SQL query is quite verbose, that's why I used a
column label.
On Apr 23, 2:01 pm, Michael Bayer <[EMAIL PROTECTED]> wrote:
> On Apr 23, 2008, at 6:55 AM, Adrian wrote:
>
>
>
>
>
> > I guess the solution to my problem is simple, although I did not
> > manage to find it.
>
> > The problem is as follows: I calculate the bray-curtis distance
> > between an input and the rows in my table and give the value an alias
> > ('brayCurtis'). What I want is to order the resultSet by brayCurtis
> > and return only those rows where the value exceeds a given threshold.
> > Ordering is pretty simple
>
> > query = query.order_by(query.c.brayCurtis.desc())
>
> > The problem occurs if I want to add an additional where clause like
> > this:
>
> > query = query.where(query.c.brayCurtis >= cutoff)
>
> > Suddenly the whole statement is duplicated. Maybe there is a more
> > concise and elegant solution for this problem.
>
> Im not sure what "cutoff" is, but if its a SELECT statement, try using
> cutoff.as_scalar(). This would solve the issue of extra elements
> being added to the FROM clause, if thats whats happening.
>
> But also, if cutoff is in fact a subquery, and the issue is that a
> labeled column from the "columns" clause is not appearing as that
> label in the WHERE clause, the subquery needs to be rendered at that
> point. SQL doesnt officially allow order by or WHERE criterion based
> on labels in the columns clause of the select statement - it will fail
> on some databases, so SQLA currently doesn't place labels (i.e. column
> AS foobar) anywhere outside the columns clause. A good optimizer
> should figure out that the columns clause and WHERE clause are the
> same thing.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
