how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?

[Mark Aquino]

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

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? 

Thanks!

[Simon King]

Out of interest, what is the point of having a visible_id column in
the B table? I'm having difficulty imagining what it would mean to
have an instance of B (which due to inheritance is also an instance of
A) which has different values in A.visible_id and B.visible_id.

Simon

[Mark Aquino]

There's no point in really having the visible_id on the A table, other than for inheritance.

The point of it being on B (and C, D, E, F, etc.) is that they have unique sequences populating those "Visible IDs", so I have can have a B-1 and a C-1 and a D-1.

In other words I have my parent table A with 

id, visible_id, type

1, 1, "B"

2, 2, "C"

3, 3, "D"

4, 4, "E"

B

id, visible_id

-----------

1, 1

C

id, visible_id

------------

2, 1

etc.

The alternative (I suppose) would be somehow configuring A.visible_id to use a different sequence for every child class table, although a.) i dont know if you can do that and b.) it's less desirable to have duplicate values in that column.

[Mike Bayer]

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+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/9a248c3e-ec1a-4e18-b663-3a03bab027e5%40googlegroups.com.

[Mark Aquino]

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

I believe you’re describing the behavior I currently have, I.e. if I query B then I can get b.visible_id otherwise I get A.visible_id.

I see your point about efficiency, but I think I’m already doing this sort of join under the hood because I’m using GraphQL to query all A and then pulling attributes on subclasses of A depending on the type.

I’m fine with eliminating visible_id from all subclasses, though, but I’d like separate numbering for each subclass.

Is it possible to specify the sequence to use per class when persisting the entities or do you have a method for that which you recommend?

If not, can you tell me how to do the mapping with coalesce and I’ll stress test it to see if it will work performance wise?

[Mike Bayer]

On Sat, Feb 15, 2020, at 3:27 PM, Mark Aquino wrote:

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

I believe you’re describing the behavior I currently have, I.e. if I query B then I can get b.visible_id otherwise I get A.visible_id. 

I see your point about efficiency, but I think I’m already doing this sort of join under the hood because I’m using GraphQL to query all A and then pulling attributes on subclasses of A depending on the type. 

I’m fine with eliminating visible_id from all subclasses, though, but I’d like separate numbering for each subclass. 

Is it possible to specify the sequence to use per class when persisting the entities or do you have a method for that which you recommend?

your base table "a" has a "type" column.   So just one visible_id column that is in a unique constraint with "type" and that would give you independent unique sequences per subclass.

(1, "a")

(2, "a")

(1, "b")

(2, "b")

(3, "a")

... etc

the next sequence value for a particular subclass is "SELECT MAX(id) + 1 FROM table WHERE type='<name>'"

If not, can you tell me how to do the mapping with coalesce and I’ll stress test it to see if it will work performance wise?

-- 

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/2348bb1b-df75-46aa-b43a-458a13d37f99%40googlegroups.com.