I couldn't figure out a better description or explanation on what I'm trying to do so I instead wrote a sql query that I want to perform and tried getting as close as possible to a result that I wanted. I wanted to know if there was a better way of doing this.
t = text(""" SELECT transactions.*, orig."Name" as orig_name, orig."Alpha code" as orig_alpha_code, orig."latitude" as orig_lat, orig."longitude" as orig_long, dest."Name" as dest_name, dest."Alpha code" as dest_alpha_code, dest."latitude" as dest_lat, dest."longitude" as dest_lon FROM transactions JOIN ( SELECT states.*, lonlats.* FROM "FIPS_States" states JOIN state_latlon lonlats on states."Alpha code"=lonlats.state ) orig ON orig."Numeric code" = transactions."ORIG_STATE" JOIN ( SELECT states.*, lonlats.* FROM "FIPS_States" states JOIN state_latlon lonlats on states."Alpha code"=lonlats.state ) dest ON dest."Numeric code" = transactions."DEST_STATE" WHERE dest."Alpha code"=:dest_code AND orig."Alpha code" =:orig_code LIMIT 100 """) # My closest way of approximating this with sqlalchemy, seems too cumbersome orig = transactions.join( state_latlon_joined, onclause=transactions.c.ORIG_STATE == FIPS_States.c.get("Numeric code")) s = select([transactions, FIPS_States.c.get("Alpha code").label("orig_state_alpha_code"), FIPS_States.c.Name.label("orig_state_name"), state_latlon.c.latitude.label('orig_lat'), state_latlon.c.longitude.label('orig_lon') ]).select_from(orig).limit(10) dest = transactions.join( state_latlon_joined, onclause=transactions.c.DEST_STATE == FIPS_States.c.get("Numeric code")) # use the previous select statement as a basis for the new select statement new_table = (select([s.alias(), FIPS_States.c.get("Alpha code").label("dest_state_alpha_code"), FIPS_States.c.Name.label("dest_state_name"), state_latlon.c.latitude.label('dest_lat'), state_latlon.c.longitude.label('dest_lon')]) .select_from(dest).limit(10)) -- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.