[sqlalchemy] Re: Consolidate multiple one-to-one into a list
Thank you, Jonathan. I’ve used SQLA’s association proxies before, I’ll take a look again. You bring up a good point, though: Ok, so this isn't a one-to-one relationship, but a many-to-many > relationship. > That’s something I’ve been debating with myself for a while before I posted here: the kind of relationship here. A Child can have only a single Parent, but a Parent has multiple Children. At first is looks like a 1-to-many, but the oddity is the “type” of the Child expressed through a named foreign key constraint: “youngest_child” and “oldest_child” are the examples here. The reason why it’s done this way is because a Child should not have knowledge of its type and how the Parent views the Child. It’s always possible to explicitly enumerate the Child objects on the Parent: children = [youngest_child, oldest_child] but I am curious if there is a better way to do that, one that involves less typing and would pick changes (e.g. adding a “shortest_child” or some such) transparently. Cheers, Jens -- 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/26803606-3d2e-48f1-b10a-2ac07cd23e94%40googlegroups.com.
[sqlalchemy] Re: Consolidate multiple one-to-one into a list
On Friday, April 17, 2020 at 8:02:50 PM UTC-4, Jens Troeger wrote: > > > Indeed, Child does have multiple parents… > Ok, so this isn't a one-to-one relationship, but a many-to-many relationship. I would opt for a 3 table structure: Parent Parent_2_Child Child The AssociationProxy extension (part of SqlAlchemy itself) can transparently map the Parent and Child relationships through the Parent_2_Child table as attributes on Parent & Child tables itself. ( https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html) The section "Simplifying Association Objects" ( https://docs.sqlalchemy.org/en/13/orm/extensions/associationproxy.html#simplifying-association-objects) gives a good example of this on User/UserKeyword/Keyword You could even remove the `oldest_child_id` and `youngest_child_id` columns, and turn them into readonly attributes that can compute the child as needed. SqlAlchemy is very robust and you can do almost anything with it. I am certain it is possible to build something that only uses the columns on the Parent table to accomplish exactly what you desire... but it really sounds like you should be using an association table in this model. -- 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/746f8e4d-c61a-4a74-9812-faecce7b0efa%40googlegroups.com.
