Dynamically constructing joins

[Horcle]

I have a situation where I can have an arbitrary number of subqueries that need to be joined on the last step, except if the number of queries, n, is 1.

For example, for n = 1, suppose I have a complex query set to the variable A[1]

The final submitted query would then look like:

        query = db.session.query(label('sid',

                                     distinct(A[1].c.patient_sid)))

Easy enough!

Now, suppose, I have two complex queries, A[1] and A[2] that are then joined as such:

        query = db.session.query(label('sid',

                                   distinct(A[1].c.patient_sid))). \

            join(A[2],A[2].c.patient_sid==a[1].c.patient_sid)

Not too bad...

Now, I have an arbitrary number of complex queries, A[1]...A[n] that need to be joined:

  query = db.session.query(label('sid',

                                   distinct(A[1].c.patient_sid))). \

            join(A[2],A[2].c.patient_sid==a[1].c.patient_sid). \

            ....

            join(A[n],A[n].c.patient_sid==a[1].c.patient_sid)

The above works fine, when I have conditionals based on the number n of queries, e.g.,

        if (n == 1):

        query = db.session.query(label('sid',

                                     distinct(a[1].c.patient_sid)))

        if (n == 2):

        query = db.session.query(label('sid',

                                   distinct(a[1].c.patient_sid))). \

            join(a[2],a[2].c.patient_sid==a[1].c.patient_sid)

        if (n == 3):

        query = db.session.query(label('sid',

                                       distinct(a[1].c.patient_sid))). \

            join(a[2],a[2].c.patient_sid==a[1].c.patient_sid). \

            join(a[3],a[3].c.patient_sid==a[1].c.patient_sid)

etc., but since I can have an arbitrary number of these queries that need to be joined, not only is use of conditionals to set up the correct form of my join inefficient, it is highly redundant and would be a huge mess.

Thus, I am wondering if it is possible to construct my join somehow, like:

    if (n == 1):

        query = 'db.session.query(label('sid', distinct(a[1].c.patient_sid)))'

    elif (n > 1):

        query = 'db.session.query(label('sid', distinct(a[1].c.patient_sid)))'

        for i in range (0,n)

            query += '.join(A[i],A[i].c.patient_sid==a[1].c.patient_sid)'

    eval(query)

I've tried all sorts of crazy things, but cannot get the general case to work.

Thanks in advance!

Greg--

-- 

Greg M. Silverman

Senior Developer Analyst

Cardiovascular Informatics 

University of Minnesota

        

        

[Jonathan Vanasco]

any reason why you're not building a query like this?

   query = db.session.query(label('sid',

                                     distinct(a[1].c.patient_sid)))

   if n >= 2

      query = query.\

            join(a[2],a[2].c.patient_sid==a[1].c.patient_sid)

   if n >= 3

      query = query.\

            join(a[3],a[3].c.patient_sid==a[1].c.patient_sid)

or

   query = db.session.query(label('sid',

                                     distinct(a[1].c.patient_sid)))

   for i in range(2, n):

      query = query.\

            join(a[i],a[i].c.patient_sid==a[1].c.patient_sid)

[Greg Silverman]

Ha! Ha! On my previous attempts, I had something similar to this, but instead, I had

query = db.session.query(label('sid',

                                     distinct(a[1].c.patient_sid)))

if (n > 1):

    for table in join_tables[1:]:

        for criterion in join_criteria[1:]:

            query = query.join(eval(table), eval(criterion))

Where the variables table and criterion were built lists, so that I ended up doing a Cartesian product of all my tables, which was giving me many problems, with aliasing being the least of it! 

Thanks!

Greg--

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/SySyi4CCCUY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

--

Greg M. Silverman

Senior Developer Analyst

Cardiovascular Informatics 

University of Minnesota

612-626-0919  

g...@umn.edu

 ›  flora-script ‹

 ›  grenzi.org  ‹

 ›  evaluate-it.org  ‹

[Jonathan Vanasco]

Yeah, there's no reason to touch eval -- and a lot of reasons not to.  Security issues aside, when you make a mistake the error will be completely unintelligible.

You can create joins dynamically very easily by just iteratively building up on it, and using getattr() if needed.

If you're doing any advanced things (subqueries, aliases, etc), I would suggest keeping the online docs loaded in a browser window and paying close attention to the return values.  Most operations will return a query, but a few will return another object.

[Horcle]

eval() was definitely not doing what I expected. Thanks for the tip about getattr(), and thanks for helping get my head screwed on right!

Greg--