First of all, thanks for your response.
1. That makes more sense.
2. I use a select first, because there are actually more than 2 types of
circuits, so there should be more models to be mapped to T_circirt.
There are some models don't have vendor but need to be mapped to the
consistent model T_Circuit,
struct T_Circuit {
1. asset_id
2. vendor_id
3. vendor
}
So what I did is first select from the original model(the other model other
than transit, transport, but dc) and the use like:
c3_select = select([
AssetDCCircuit.id,
literal_column('NULL').label('vendor_id'),
])
And then map the T_Circiut to it:
c3_mapper = mapper(
TCircuit,
c3_select,
primary_key=[c3_select.c.id <http://c2_select.c.id/>],
properties={
'asset_id': c3_select.c.id <http://c2_select.c.id/>,
'vendor_id': c3_select.c.vendor_id,
'vendor': relationship(
vendor_mapper,
foreign_keys=[c3_select.c.vendor_id],
),
)
The struct is a struct type of thrift service.
Is there any way to resolve this avoiding the subquery?
3. T_Circuit is actully TT_Circuit, it's a typo.
On Wednesday, October 26, 2016 at 10:12:38 AM UTC-7, Mike Bayer wrote:
>
>
>
> On 10/26/2016 12:03 PM, Alfred Soeng wrote:
> >
> > 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()
>
> So there's some things to happen here:
>
> 1. Don't call "joinedload" on the Query that you're going to be putting
> into a union. the "joinedload" is an option that attaches to the final
> list of objects that you will be iterating. It doesn't make sense to
> embed it inside of a SELECT that is going to be part a bigger statement.
>
> 2. setting up a mapper() of a select() is going to create more
> subqueries and complicate things. The two selects you have don't
> even seem to have any WHERE clause or anything, so I don't see why these
> extra mappers are necessary. It would work a lot better just to map to
> the underlying asset_transit_circuit and asset_transport_circuit tables
> directly. All the mapper() calls here seem odd - classical mapping
> is very seldom used and especially in conjunction with declarative,
> there are very few reasons you'd want to do that.
>
> 3. while the information here gives me a few more clues, it's still not
> enough for me to really see what you're doing. I don't see what
> T_Vendor is (or how T_Vendor does anything that Vendor doesn't, why not
> just "T_Vendor = Vendor" ?) , I don't see what TCircuit is (I see the
> odd "struct" notation, but that's not Python - is TCircuit just an empty
> class? Why is it mapped with mapper() and not a declarative class like
> the others?) Overall there's a lot of odd patterns here that aren't
> clear why they are necessary.
>
> Hopefully #1 is all you need here, though I'd want to clarify what the
> rationale is for all the odd patterns because there is likely a much
> simpler way to accomplish whatever it is.
>
>
>
>
>
>
> >
> >
> > 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
> > <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 <http://anon_1.id> AS anon_1_id,
> anon_1.vendor_id
> > AS anon_1_vendor_id,
> > > vendor_1.id <http://vendor_1.id> AS vendor_1_id, vendor_1.name
> > <http://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
> > <http://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 <http://vendor_1.id> = anon_1.vendor_id
> > >
> > >
> > > q2:
> > >
> > > SELECT anon_1.id <http://anon_1.id> AS anon_1_id,
> anon_1.vendor_id
> > AS anon_1_vendor_id,
> > > vendor_1.id <http://vendor_1.id> AS vendor_1_id, vendor_1.name
> > <http://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
> > <http://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 <http://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 <http://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 <http://vendor_1.id> AS
> vendor_1_id,
> > vendor_1.name <http://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 <http://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 <http://anon_2.id> AS
> > anon_2_id \nFROM (SELECT
> > > asset_transport_circuit.id <http://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 <http://anon_2.id> IN
> > > (%s, %s, %s) UNION SELECT anon_3.id <http://anon_3.id> AS
> > anon_3_id \nFROM (SELECT
> > > asset_transit_circuit.id <http://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 <http://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 <http://dc_select.c.id>],
> > >
> > > properties={
> > >
> > > 'asset_id': dc_select.c.id <http://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
> > <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:>
> <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
> > <https://groups.google.com/group/sqlalchemy>.
> > > For more options, visit https://groups.google.com/d/optout
> > <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+...@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.
