On 10/26/2016 02:09 PM, Alfred Soeng wrote:
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
}
what is a "struct" in this context?
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?
why not use traditional concrete table inheritance? that's mostly what
this looks like. You'd only use concrete if you truly need to query
for TT_Circuit objects from multiple concrete tables at once, e.g. as
the UNION case is here. If it were me, I'd try to avoid that if we are
working with small datasets (would just run several queries against
different classes that all have TT_Circuit as an abstract base). But if
you want a unified "TT_Circuit" query that hits multiple tables
regularly using UNION, the pattern at
http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#concrete-table-inheritance
shows how to do this both with mapper() and with declarative. The
union would be generated from the polymorphic_union function.
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>
> <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> <http://anon_1.id> AS
anon_1_id, anon_1.vendor_id
> AS anon_1_vendor_id,
> > vendor_1.id <http://vendor_1.id> <http://vendor_1.id> AS
vendor_1_id, vendor_1.name <http://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>
> <http://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>
<http://vendor_1.id> = anon_1.vendor_id
> >
> >
> > q2:
> >
> > SELECT anon_1.id <http://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> <http://vendor_1.id> AS
vendor_1_id, vendor_1.name <http://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>
> <http://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> <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> <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>
<http://vendor_1.id> AS vendor_1_id,
> vendor_1.name <http://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>
<http://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> <http://anon_2.id> AS
> anon_2_id \nFROM (SELECT
> > asset_transport_circuit.id
<http://asset_transport_circuit.id>
<http://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> <http://anon_2.id> IN
> > (%s, %s, %s) UNION SELECT anon_3.id <http://anon_3.id>
<http://anon_3.id> AS
> anon_3_id \nFROM (SELECT
> > asset_transit_circuit.id <http://asset_transit_circuit.id>
<http://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> <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>
<http://dc_select.c.id>],
> >
> > properties={
> >
> > 'asset_id': dc_select.c.id <http://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>
> <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>
> <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>
> <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
<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
<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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.
