On Fri, Feb 14, 2020, at 12:35 PM, Mark Aquino wrote:
> I have a polymorphic class structure like this, with a lot of classes 
> extending the parent class. 
> In reality I'm using a Mixin that declares the visible_id column and it's 
> defined with @declared_attr.cascading, but for simplicity:
> 
> 
> 
> class A(Base):
>  __tablename__ = 'a'
>  id = Column(Integer, primary_key=True)
>  visible_id = Column(Integer)
> 
> class B(A):
>  __tablename__ = 'b'
>  id = Column(Integer, ForeignKey("A.id"), primary_key=True)
>  visible_id = Column(Integer)
> 
> 
> What I need for my application is to query A.visible_id and return the CHILD 
> values for B.visible_id (and all the others).

this mapping is already breaking some of the assumptions of the joined table 
inheritance feature which states that a particular named attribute isn't 
masking the value of a superclass.

the above mapping emits this warning:

SAWarning: Implicitly combining column a.visible_id with column b.visible_id 
under attribute 'visible_id'. Please configure one or more attributes for these 
same-named columns explicitly.



> 
> The inheritance works fine, i.e. if i query all As in the database, my 
> response is a list of [B] objects, but unless I directly query B the 
> visible_id from A takes precedence and I cannot query A.visible_id if I 
> remove it from A.
> 
> Can anyone tell me how to configure this? 


at the SQL level, as written you'd need there to be a COALESCE function ( or a 
similar CASE expression) for this to work as is:

SELECT COALESCE(b.visible_id, a.visible_id) FROM a LEFT OUTER JOIN b ON ...

this is assuming when a row comes back that is not a "b", you want 
a.visible_id, and when a row *is* a "b", you want b.visible_id. This is why the 
mapping is awkward.

so at the mapping level I would not have these two visible_id columns be under 
the same attribute name, because they represent different values. You can use a 
@property so that when you get back an A or a B object , they seek to return 
either the column on A or the column on B. That's at least the "simple" way to 
do this. if you wanted to work the COALESCE into the mapping, that can be done 
also but it is more complicated and your queries for A will not be as efficient 
since the whole mapping would need to be against a subquery.

As for individual column access. using joined inheritance without any special 
options, this query:

s.query(A.visible_id)

does not query the B table at all. so again, in order for "A.visible_id" to 
imply a query against other tables as well implies you'd need to map against a 
subquery that includes the COALESCE phrase.

I can show you how to do this but it's going to make for much less efficient 
queries at the SQL level because it will be using subqueries. It would be 
preferable to solve your problem in a simpler way.











> 
> Thanks!
> 

> --
>  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 view this discussion on the web visit 
> https://groups.google.com/d/msgid/sqlalchemy/9a248c3e-ec1a-4e18-b663-3a03bab027e5%40googlegroups.com
>  
> <https://groups.google.com/d/msgid/sqlalchemy/9a248c3e-ec1a-4e18-b663-3a03bab027e5%40googlegroups.com?utm_medium=email&utm_source=footer>.

-- 
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 view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/826654ab-334d-475e-b9a4-1b381eb8ed2d%40www.fastmail.com.

Reply via email to