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 > > <https://groups.google.com/d/msgid/sqlalchemy/84058464-5b92-4305-a348-d5a65fba441fn%40googlegroups.com?utm_medium=email&utm_source=footer> > . > > > -- 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.