> On 23 Sep 2015, at 20:38, Jonathan Vanasco <jonat...@findmeon.com> wrote:
>
> I have a query where I derive an object based on some dynamic filters on a
> relationship:
>
> sql_ors = [
> sqlalchemy.func.lower(Bar.string_id) == id_search.lower(),
> sqlalchemy.func.lower(Bar.numeric_id) == id_search.lower(),
> ]
> query = dbSession.query(Foo)\
> .join(Bar,
> Foo.bar_id == Bar.id
> )\
> .filter(
> Foo.bash.op('IS')(True),
> sqlalchemy.sql.expression.or_(*sql_ors),
> )
>
> This generally works fine.
>
> Because of how the app stores data, I need to expand this query in the
> following way:
>
> 1. I need to join another instance of Bar onto the query
> 2. I need to filter against that instance of Bar
>
> After reading the docs, I was hoping something like this would work -- it did
> not, but I'm sharing this to explain the actions I was trying to accomplish
>
> _aliased = sqlalchemy.orm.alias(Bar, name='bar2')
> sql_ors.extend([
> sqlalchemy.func.lower(_aliased.string_id) == id_search.lower(),
> sqlalchemy.func.lower(_aliased.numeric_id) == id_search.lower(),
> ])
> query = query.join(_aliased, Foo.bar_id == _aliased.id)
>
>
> A better query would handle this entire section with some CASE clauses, but
> I'm hoping to just patch in the right functionality for a bit without
> rewriting too much.
>
> Anyone have a clue which ORM api elements I should be using?
That looks like the right approach. There is probably something else in your
actual code that is causing "it [to] not work".
To get a better understanding of "it did not work", I would look at the
"str(query)" before and after the stuff with the aliased table.
--
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
