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