Re: [sqlalchemy] how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?
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=''" > > 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+unsubscr...@googlegroups.com. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/2348bb1b-df75-46aa-b43a-458a13d37f99%40googlegroups.com. > -- 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/dc0aa15a-9766-4556-8f81-fdae70b20a3f%40www.fastmail.com.
Re: [sqlalchemy] how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?
“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? -- 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/2348bb1b-df75-46aa-b43a-458a13d37f99%40googlegroups.com.
Re: [sqlalchemy] how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?
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
>
> .
--
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.
Re: [sqlalchemy] how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?
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.
On Friday, February 14, 2020 at 12:55:47 PM UTC-5, Simon King wrote:
>
> On Fri, Feb 14, 2020 at 5: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).
> >
> > 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?
> >
>
> 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
>
--
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/546e5a4a-7ab5-4350-888a-6f8d54fb822c%40googlegroups.com.
Re: [sqlalchemy] how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?
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"
1, 2, "C"
1, 3, "D"
1, 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.
On Friday, February 14, 2020 at 12:55:47 PM UTC-5, Simon King wrote:
>
> On Fri, Feb 14, 2020 at 5: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).
> >
> > 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?
> >
>
> 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
>
--
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/6dcc5932-64f4-4aaa-b766-a747d285e132%40googlegroups.com.
Re: [sqlalchemy] how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?
On Fri, Feb 14, 2020 at 5: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).
>
> 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?
>
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
--
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/CAFHwexf305mqE_NRqxF2Pv1H9j9ngt6wVHpLmWaVRHi2Ot4o2g%40mail.gmail.com.
[sqlalchemy] how to "explicitly" combine columns, for example get the child class value from a query on parent class from same named column?
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!
--
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.
