Hi Mike, Are you free to talk through the fb messenger about my issue if you are good to it?
Best, Alfred On Tue, Oct 25, 2016 at 2:44 PM, mike bayer <[email protected]> wrote: > In a case like this I don't know what the problem is without being able to > run an example. The guidelines at http://stackoverflow.com/help/mcve > would make this easiest. Can we see complete mappings / table information > (only what's needed to reproduce the problem) as well as how you are > creating these Query and/or select() objects (it seems these are select() )? > > Also need version of SQLAlchemy in use. > > thanks! > > > > > > On 10/25/2016 04:48 PM, Alfred Soeng wrote: > >> When I union 2 queries, it seems combine the same relationship together >> and cause the alias problem >> In the code, it generated 2 queries like: >> q1: >> >> SELECT anon_1.id AS anon_1_id, anon_1.vendor_id AS anon_1_vendor_id, >> vendor_1.id AS vendor_1_id, vendor_1.name AS vendor_1_name, >> vendor_1.sf_id AS vendor_1_sf_id, vendor_1.transit_as_id AS >> vendor_1_transit_as_id, vendor_1.created_by AS vendor_1_created_by, >> vendor_1.created_at AS vendor_1_created_at, vendor_1.updated_by AS >> vendor_1_updated_by, vendor_1.updated_at AS vendor_1_updated_at, >> vendor_1.active AS vendor_1_active >> >> FROM (SELECT asset_transport_circuit.id AS id, >> asset_transport_circuit.vendor_id AS vendor_id >> >> FROM asset_transport_circuit) AS anon_1 LEFT OUTER JOIN vendor AS >> vendor_1 ON vendor_1.id = anon_1.vendor_id >> >> >> q2: >> >> SELECT anon_1.id AS anon_1_id, anon_1.vendor_id AS anon_1_vendor_id, >> vendor_1.id AS vendor_1_id, vendor_1.name AS vendor_1_name, >> vendor_1.sf_id AS vendor_1_sf_id, vendor_1.transit_as_id AS >> vendor_1_transit_as_id, vendor_1.created_by AS vendor_1_created_by, >> vendor_1.created_at AS vendor_1_created_at, vendor_1.updated_by AS >> vendor_1_updated_by, vendor_1.updated_at AS vendor_1_updated_at, >> vendor_1.active AS vendor_1_active >> >> FROM (SELECT asset_transit_circuit.id AS id, >> asset_transit_circuit.vendor_id AS vendor_id >> >> FROM asset_transit_circuit) AS anon_1 LEFT OUTER JOIN vendor AS vendor_1 >> ON vendor_1.id = anon_1.vendor_id >> >> >> But when I called the q1.union(q2), it said an column issue because the >> union merge the same 'LEFT OUTER JOIN vendor AS vendor_1 ON vendor_1.id >> = anon_1.vendor_id' together and cause a alias problem: >> >> >> q1.union(q2): >> >> (1054, "Unknown column '`anon_2`.`vendor_id`' in 'on clause'") [SQL: >> 'SELECT anon_1.anon_2_id AS anon_1_anon_2_id, anon_2.vendor_id AS >> anon_2_vendor_id, vendor_1.id AS vendor_1_id, vendor_1.name AS >> vendor_1_name, vendor_1.sf_id AS vendor_1_sf_id, vendor_1.transit_as_id >> AS vendor_1_transit_as_id, vendor_1.created_by AS vendor_1_created_by, >> vendor_1.created_at AS vendor_1_created_at, vendor_1.updated_by AS >> vendor_1_updated_by, vendor_1.updated_at AS vendor_1_updated_at, >> vendor_1.active AS vendor_1_active \nFROM (SELECT >> asset_transport_circuit.id AS id, asset_transport_circuit.vendor_id AS >> vendor_id \nFROM asset_transport_circuit) AS anon_2, (SELECT /* >> <string>:2 union --/--/--/-- */ anon_2.id AS anon_2_id \nFROM (SELECT >> asset_transport_circuit.id AS id, asset_transport_circuit.vendor_id AS >> vendor_id \nFROM asset_transport_circuit) AS anon_2 \nWHERE anon_2.id IN >> (%s, %s, %s) UNION SELECT anon_3.id AS anon_3_id \nFROM (SELECT >> asset_transit_circuit.id AS id, asset_transit_circuit.vendor_id AS >> vendor_id \nFROM asset_transit_circuit) AS anon_3 \) AS anon_1 LEFT >> OUTER JOIN vendor AS vendor_1 ON vendor_1.id = anon_2.vendor_id' >> >> >> The 2 mappers are the same but different name: like below: >> >> >> dc_select = >> select([AssetTransportCircuit.id, AssetTransportCircuit.vendor_id,]) >> >> >> class TT_Circuit(object): >> >> def __init__(self, asset_id, vendor_id, vendor): >> >> self.asset_id = asset_id >> >> self.vendor_id = vendor_id >> >> self.vendor = vendor >> >> >> circuit_mapper = mapper( >> >> TT_Circuit, >> >> dc_select, >> >> primary_key=[dc_select.c.id], >> >> properties={ >> >> 'asset_id': dc_select.c.id, >> >> 'vendor_id': dc_select.c.vendor_id.label('vendor_id'), >> >> 'vendor': relationship( >> >> vendor_mapper, >> >> primaryjoin=and_( >> >> dc_select.c.vendor_id == foreign(Vendor.id), >> >> foreign(Vendor.active), >> >> ), >> >> uselist=False, >> >> viewonly=True, >> >> ), >> >> } >> >> ) >> >> >> Is there a way to resolve the problem? >> >> -- >> 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 [email protected] >> <mailto:[email protected]>. >> To post to this group, send email to [email protected] >> <mailto:[email protected]>. >> Visit this group at https://groups.google.com/group/sqlalchemy. >> For more options, visit https://groups.google.com/d/optout. >> > > -- > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit https://groups.google.com/d/to > pic/sqlalchemy/kHLmMxAlmxw/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
