Possible to pass array of (table, conditions) to join() like where()/select() ?

[mkmo...@gmail.com]

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

[Mike Bayer]

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.

[mkmo...@gmail.com]

Thanks!