struct T_AssetTransitCircuit {
1. asset_id
2. vendor_id
3. vendor
}
struct T_AssetTransportCircuit {
1. asset_id
2. vendor_id
3. vendor
}
struct T_Vendor {
1: i32 id,
2: optional string name,
3: optional string sf_id,
4: optional string transit_as_id,
5: optional i64 created_by,
6: optional i64 created_at,
7: optional i64 updated_by,
8: optional i64 updated_at,
9: optional bool active
}
struct T_Circuit {
1. asset_id
2. vendor_id
3. vendor
}
class Vendor:
__tablename__ = 'vendor'
id = Column(DBT.UNSIGNED_INT10, primary_key=True, autoincrement=True)
name = Column(DBT.STRING)
sf_id = Column(DBT.STRING, unique=True, nullable=False)
transit_as_id = Column(DBT.STRING)
created_by = Column(mysql.BIGINT(20))
created_at = Column(NumericDatetime, default=current_timestamp)
updated_by = Column(mysql.BIGINT(20))
updated_at = Column(
NumericDatetime,
nullable=False,
default=current_timestamp,
onupdate=current_timestamp)
active = Column(DBT.UNSIGNED_SMALLINT, nullable=False, default=1)
vendor_mapper = mapper(
T_Vendor,
Vendor.__table__,
)
class AssetTransitCircuit:
__tablename__ = 'asset_transit_circuit'
id = Column(
DBT.UNSIGNED_INT_ID,
ForeignKey("fb_asset.id"),
primary_key=True,
)
vendor_id = Column(DBT.UNSIGNED_INT10, ForeignKey("vendor.id"))
class AssetTransportCircuit:
__table__ = 'asset_transport_circuit'
id = Column(
DBT.UNSIGNED_INT_ID,
ForeignKey("fb_asset.id"),
primary_key=True,
)
vendor_id = Column(DBT.UNSIGNED_INT10, ForeignKey("vendor.id"))
c1_select = select([
AssetTransportCircuit.id,
AssetTransportCircuit.vendor_id,
])
c2_select = select([
AssetTransitCircuit.id,
AssetTransitCircuit.vendor_id,
])
c1_mapper = mapper(
TCircuit,
c1_select,
primary_key=[c1_select.c.id],
properties={
'asset_id': c1_select.c.id,
'vendor_id': c1_select.c.vendor_id,
'vendor': relationship(
vendor_mapper,
foreign_keys=[c1_select.c.vendor_id],
),
)
c2_mapper = mapper(
TCircuit,
c2_select,
primary_key=[c2_select.c.id],
properties={
'asset_id': c2_select.c.id,
'vendor_id': c2_select.c.vendor_id,
'vendor': relationship(
vendor_mapper,
foreign_keys=[c2_select.c.vendor_id],
),
)
q1
=
session.query(c1.mapper._class).options(joinedload('vendor')).options(load_load('id'))
q2
=
session.query(c2.mapper._class).options(joinedload('vendor')).options(load_load('id'))
q1.union(q2).all()
On Wednesday, October 26, 2016 at 3:42:51 AM UTC-7, Mike Bayer 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 sqlalchemy+...@googlegroups.com <javascript:>
> > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>
> > <mailto:sqlal...@googlegroups.com <javascript:>>.
> > 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 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.
