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 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.
