Re: [sqlalchemy] Possible to pass array of (table, conditions) to join() like where()/select() ?
Thanks!
On Friday, September 16, 2022 at 10:53:59 AM UTC-7 Mike Bayer wrote:
>
>
> On Fri, Sep 16, 2022, at 12:10 PM, mkmo...@gmail.com wrote:
>
> I use the following pattern in my REST APIs, building up the select,
> joins, where conditions, group bys, order bys, depending on the query
> parameters passed in by the user:
>
> selects = [Foo]
> joins = [(Bar, Foo.c.id == Bar.c.foo_id)]
> where_conditions = [Foo.c.id == request.args['pk']]
>
> if request.args.get('include_baz'):
> selects.append(Baz)
> joins.append((Baz, Bar.c.id == Baz.c.bar_id))
>
> What I would like to do is the following:
>
> sel = select(
> *selects
> ).join(
> *joins # doesn't work
> ).where(
> *where_conditions
> )
>
> This works for everything except for `join` and `outerjoin`. So I have to
> write it like this:
>
> sel = select(*selects)
> for table, condition in joins:
> sel = sel.join(table, condition)
> sel = se.where(*where_conditions)
>
> Is there some way to perform a join by passing an array of (table,
> conditions) so I can write the SQL without all of the `sel = sel. ` noise?
>
>
> if you have explicit join conditions like that, you might be able to
> make them into join objects:
>
> from sqlalchemy.orm import join
> sel.join(*[join(left, right, onclause) for right, onclause in conditions])
>
> IMO that's not really any better, or you can make a def like this:
>
> def join(stmt, conditions):
> for table, condition in conditions:
>stmt = stmt.join(table, condition)
> return stmt
>
> then you use it as:
>
> sel = join(sel, *joins)
>
> the form where we used to accept multiple join conditions inside of one
> join() method is part of legacy Query and is being removed. There are too
> many different argument forms for join() as it is for it to be appropriate
> for it to accept *args.
>
> personally I think "stmt = stmt.modifier(thing)" is the cleanest,
> including for the WHERE clause too.
>
>
>
> What I've been doing is using a function like the following:
>
> def collection_query(selects, joins, where_conditions, ...)
>
> But this has other problems and I would like to go back to raw sqlalchemy.
>
> Thanks and best regards,
>
> Matthew
>
>
> --
> 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+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com
>
>
> .
>
>
>
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/de7413c5-ddec-419e-8edf-e60e45573f37n%40googlegroups.com.
Re: [sqlalchemy] Possible to pass array of (table, conditions) to join() like where()/select() ?
On Fri, Sep 16, 2022, at 12:10 PM, mkmo...@gmail.com wrote:
> I use the following pattern in my REST APIs, building up the select, joins,
> where conditions, group bys, order bys, depending on the query parameters
> passed in by the user:
>
> selects = [Foo]
> joins = [(Bar, Foo.c.id == Bar.c.foo_id)]
> where_conditions = [Foo.c.id == request.args['pk']]
>
> if request.args.get('include_baz'):
> selects.append(Baz)
> joins.append((Baz, Bar.c.id == Baz.c.bar_id))
>
> What I would like to do is the following:
>
> sel = select(
> *selects
> ).join(
> *joins # doesn't work
> ).where(
> *where_conditions
> )
>
> This works for everything except for `join` and `outerjoin`. So I have to
> write it like this:
>
> sel = select(*selects)
> for table, condition in joins:
> sel = sel.join(table, condition)
> sel = se.where(*where_conditions)
>
> Is there some way to perform a join by passing an array of (table,
> conditions) so I can write the SQL without all of the `sel = sel. ` noise?
if you have explicit join conditions like that, you might be able to make them
into join objects:
from sqlalchemy.orm import join
sel.join(*[join(left, right, onclause) for right, onclause in conditions])
IMO that's not really any better, or you can make a def like this:
def join(stmt, conditions):
for table, condition in conditions:
stmt = stmt.join(table, condition)
return stmt
then you use it as:
sel = join(sel, *joins)
the form where we used to accept multiple join conditions inside of one join()
method is part of legacy Query and is being removed. There are too many
different argument forms for join() as it is for it to be appropriate for it to
accept *args.
personally I think "stmt = stmt.modifier(thing)" is the cleanest, including for
the WHERE clause too.
>
> What I've been doing is using a function like the following:
>
> def collection_query(selects, joins, where_conditions, ...)
>
> But this has other problems and I would like to go back to raw sqlalchemy.
>
> Thanks and best regards,
>
> Matthew
>
>
>
> --
> 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 view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com
>
> .
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/66aa5b23-940f-4450-b1f1-cecd25b1cad0%40www.fastmail.com.
[sqlalchemy] Possible to pass array of (table, conditions) to join() like where()/select() ?
I use the following pattern in my REST APIs, building up the select, joins,
where conditions, group bys, order bys, depending on the query parameters
passed in by the user:
selects = [Foo]
joins = [(Bar, Foo.c.id == Bar.c.foo_id)]
where_conditions = [Foo.c.id == request.args['pk']]
if request.args.get('include_baz'):
selects.append(Baz)
joins.append((Baz, Bar.c.id == Baz.c.bar_id))
What I would like to do is the following:
sel = select(
*selects
).join(
*joins # doesn't work
).where(
*where_conditions
)
This works for everything except for `join` and `outerjoin`. So I have to
write it like this:
sel = select(*selects)
for table, condition in joins:
sel = sel.join(table, condition)
sel = se.where(*where_conditions)
Is there some way to perform a join by passing an array of (table,
conditions) so I can write the SQL without all of the `sel = sel. ` noise?
What I've been doing is using a function like the following:
def collection_query(selects, joins, where_conditions, ...)
But this has other problems and I would like to go back to raw sqlalchemy.
Thanks and best regards,
Matthew
--
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 view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com.
