Relationship between a class with two different instances of other class (Newbie)
Hector Blanco
I wrote a couple of days ago about how to model an structure of three
classes (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#).
I thing I almost have it, but I am still getting problems mapping an
structure like this.
class Child(rdb.Model):
def __init__(self):
self.field1 = “hello world”
class Parent(rdb.Model):
def __init__(self):
self.child1 = Child()
self.child2 = Child()
The “Parent” class has two different instances of a Child() class. I
am not even sure about how to treat this (two different 1:1
relationships or a 1:2 relationship).
My last try is this mapping:
(don't let the rdb.Model thing fool you, is just something that
automatically maps the class to the table specified in rdb.tablename).
class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("children_table")
id = Column("id", Integer, primary_key=True)
field1 = Column(“field1”, String(64)) #Irrelevant
def __init__(self):
self.field1 = “hello world”
class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("parent_table”)
id = Column("id", Integer, primary_key=True)
child1_id = Column("child_1_id", Integer, ForeignKey("children_table.id"))
child2_id = Column("child_2_id", Integer, ForeignKey("children_table.id"))
child1 = relationship(Child,
primaryjoin = (child1_id=="children_table.id")
)
child2 = relationship(Child,
primaryjoin = (child2_id=="children_table.id")
)
I have tried (almost) everything. I say “almost” because obviously I
haven't tried the right thing. I keep getting errors that sqlalchemy
can't determine the relationship between the two tables.
It looks very similar to:
http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child
but I also understand that maybe I can't get this working that way,
because I am actually putting two ForeignKeys from the same table
(“children_table”) in the parent table. I am not sure how that will
work out, or is correct or what... :-(
Thank you!
Conor
Hello list... I wrote a couple of days ago about how to model an structure of three classes (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#
).
I thing I almost have it, but I am still getting problems mapping an
structure like this.
class Child(rdb.Model):
def __init__(self):
self.field1 = �hello world�
class Parent(rdb.Model):
def __init__(self):
self.child1 = Child()
self.child2 = Child()
The �Parent� class has two different instances of a Child() class. I
am not even sure about how to treat this (two different 1:1
relationships or a 1:2 relationship).
My last try is this mapping:
(don't let the rdb.Model thing fool you, is just something that
automatically maps the class to the table specified in rdb.tablename).
class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("children_table")
id = Column("id", Integer, primary_key=True)
field1 = Column(�field1�, String(64)) #Irrelevant
def __init__(self):
self.field1 = �hello world�
class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("parent_table�)
id = Column("id", Integer, primary_key=True)
child1_id = Column("child_1_id", Integer, ForeignKey("children_table.id"))
child2_id = Column("child_2_id", Integer, ForeignKey("children_table.id"))
child1 = relationship(Child,
primaryjoin = (child1_id=="children_table.id")
)
child2 = relationship(Child,
primaryjoin = (child2_id=="children_table.id")
)
I have tried (almost) everything. I say �almost� because obviously I
haven't tried the right thing. I keep getting errors that sqlalchemy
can't determine the relationship between the two tables.
It looks very similar to:
http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child
but I also understand that maybe I can't get this working that way,
because I am actually putting two ForeignKeys from the same table
(�children_table�) in the parent table. I am not sure how that will
work out, or is correct or what... :-(
Thank you!
Usually we consider the table with the foreign key as the "child"
table, but that's just being picky. The problem is that SQLAlchemy
is treating "children_table.id" as a literal instead of a clause,
so your join would be like (parent JOIN child ON parent.child1_id
= 'children_table.id'). Obviously that is not what you want. There
are several ways to formulate primaryjoin/secondaryjoin.
Pass the whole thing in as a string:
child1 = relationship(Child, primaryjoin="Parent.child1_id == Child.id")
Use the column objects directly (this requires that Child be
defined before Parent):
child1 = relationship(Child, primaryjoin=child1_id==Child.id)
Use a callable (my favorite):
child1 = relationship(Child, primaryjoin=lambda: Parent.child1_id == Child.id)
-Conor
Hector Blanco
The callable works like a charm! It's great news! (I've been trying to
figure out this for 3 days... yeah... I guess I'm not that smart)
Now that I have it working, a “design” question pops up. Nothing
technical, really.
As Connor mentioned in his reply:
“Usually we consider the table with the foreign key as the "child"
table, but that's just being picky”
That's very true, and now I don't know how to design it...
I can do it the way I asked or...
class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("children_table")
id = Column("id", Integer, primary_key=True)
parent_id = Column("id", Integer, ForeignKey(“parent_table.id”)) # New!
type = Column("type", ShortInteger) # New!
field1 = Column(“field1”, String(64)) #Irrelevant
def __init__(self):
self.field1 = “hello world”
class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("parent_table”)
id = Column("id", Integer, primary_key=True)
child1 = relationship(
# Well... this I still don't know how to write it down,
# but it would be something like:
# Give me all the children whose “parent_id” is my “id”
# AND type == 1
# I'll deal with the joins and that depending on your answer, guys
)
child2 = relationship(
# Would be same as above
# AND type == 2
)
This may be good for adding new children to the parent class... If I
add a “Parent.child3”, I just need to create a new relationship very
similar to the already existing ones.
The way I asked in my former question would imply creating a new
relationship AND adding a new foreign key to the parent.
I'd like to know what people that know much more about databases think :)
2010/10/29 Conor <conor.edw...@gmail.com>:
> On 10/29/2010 09:43 AM, Hector Blanco wrote:
>
> Hello list...
>
> I wrote a couple of days ago about how to model an structure of three
> classes
> (http://groups.google.com/group/sqlalchemy/browse_thread/thread/5ba5c4ad16f789d6#).
> I thing I almost have it, but I am still getting problems mapping an
> structure like this.
>
> class Child(rdb.Model):
> def __init__(self):
> self.field1 = “hello world”
>
> class Parent(rdb.Model):
> def __init__(self):
> self.child1 = Child()
> self.child2 = Child()
>
> The “Parent” class has two different instances of a Child() class. I
> am not even sure about how to treat this (two different 1:1
> relationships or a 1:2 relationship).
>
> My last try is this mapping:
>
> (don't let the rdb.Model thing fool you, is just something that
> automatically maps the class to the table specified in rdb.tablename).
>
> class Child(rdb.Model):
> rdb.metadata(metadata)
> rdb.tablename("children_table")
> id = Column("id", Integer, primary_key=True)
> field1 = Column(“field1”, String(64)) #Irrelevant
> def __init__(self):
> self.field1 = “hello world”
>
> class Parent(rdb.Model):
> rdb.metadata(metadata)
> rdb.tablename("parent_table”)
>
> id = Column("id", Integer, primary_key=True)
> child1_id = Column("child_1_id", Integer, ForeignKey("children_table.id"))
> child2_id = Column("child_2_id", Integer, ForeignKey("children_table.id"))
>
> child1 = relationship(Child,
> primaryjoin = (child1_id=="children_table.id")
> )
>
> child2 = relationship(Child,
> primaryjoin = (child2_id=="children_table.id")
> )
>
> I have tried (almost) everything. I say “almost” because obviously I
> haven't tried the right thing. I keep getting errors that sqlalchemy
> can't determine the relationship between the two tables.
>
> It looks very similar to:
> http://www.sqlalchemy.org/docs/orm/relationships.html#multiple-relationships-against-the-same-parent-child
>
> but I also understand that maybe I can't get this working that way,
> because I am actually putting two ForeignKeys from the same table
> (“children_table”) in the parent table. I am not sure how that will
> work out, or is correct or what... :-(
>
> Thank you!
>
> Usually we consider the table with the foreign key as the "child" table, but
> that's just being picky. The problem is that SQLAlchemy is treating
> "children_table.id" as a literal instead of a clause, so your join would be
> like (parent JOIN child ON parent.child1_id = 'children_table.id').
> Obviously that is not what you want. There are several ways to formulate
> primaryjoin/secondaryjoin.
>
> Pass the whole thing in as a string:
>
> child1 = relationship(Child, primaryjoin="Parent.child1_id == Child.id")
>
> Use the column objects directly (this requires that Child be defined before
> Parent):
>
> child1 = relationship(Child, primaryjoin=child1_id==Child.id)
>
> Use a callable (my favorite):
>
> child1 = relationship(Child, primaryjoin=lambda: Parent.child1_id ==
> Child.id)
>
> -Conor
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
Conor
Thanks Conor! The callable works like a charm! It's great news! (I've been trying to figure out this for 3 days... yeah... I guess I'm not that smart) Now that I have it working, a �design� question pops up. Nothing technical, really. As Connor mentioned in his reply: �Usually we consider the table with the foreign key as the "child" table, but that's just being picky� That's very true, and now I don't know how to design it...
It would be easier to design it if we had more concrete names
instead of "Parent" and "Child". What is the actual use case? Is
this a tree hierarchy? Does each parent have exactly two children?
I can do it the way I asked or...
class Child(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("children_table")
id = Column("id", Integer, primary_key=True)
parent_id = Column("id", Integer, ForeignKey(�parent_table.id�)) # New!
type = Column("type", ShortInteger) # New!
field1 = Column(�field1�, String(64)) #Irrelevant
def __init__(self):
self.field1 = �hello world�
class Parent(rdb.Model):
rdb.metadata(metadata)
rdb.tablename("parent_table�)
id = Column("id", Integer, primary_key=True)
child1 = relationship(
# Well... this I still don't know how to write it down,
# but it would be something like:
# Give me all the children whose �parent_id� is my �id�
# AND type == 1
# I'll deal with the joins and that depending on your answer, guys
)
child2 = relationship(
# Would be same as above
# AND type == 2
)
This may be good for adding new children to the parent class... If I
add a �Parent.child3�, I just need to create a new relationship very
similar to the already existing ones.
The way I asked in my former question would imply creating a new
relationship AND adding a new foreign key to the parent.
I'd like to know what people that know much more about databases think :)
I'm confused as to why you would want separate "child1",
"child2", etc. relationships instead of a single "children"
relationship. Is Child.type really something you want for
distinguishing children, or is it something you added to try and
make the relationships work?
Assuming you really do want to keep separate "child1" and
"child2" relationships, and they are both one-to-one
relationships, they would look like this:
# omit uselist=False if this is a one-to-many relationship
child1 = relationship(
"Child",
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1),
uselist=False)
child2 = relationship(
"Child",
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2),
uselist=False)
-Conor
Hector Blanco
Well... I'm afraid I need to keep it separated. In the real model each
child is a list that is accessed in different ways (and do different
things) depending on which child it is.
I was wondering what it would be better (more correct) from a
"relational" point of view:
Keeping the ids of the children in the parent (as foreign keys) or
having the id of the parent in the children. I guess from a relational
point of view it's more correct having the id of the parent in the
children, right?
So I think it's better if I go with Conor's last solution:
child1 = relationship(
"Child",
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1),
uselist=False)
child2 = relationship(
"Child",
primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2),
uselist=False)
I guess the relationship in this case is a 1:N (1 parent has 2
children) and it sounds "right" to keep the id of the parent in the
children, and not viceversa.
I also asked the same question in stackoverflow
(http://stackoverflow.com/questions/4055332/relational-database-design-two-relations-11-or-one-12),
and someone said it's more correct this last approach (of keeping the
parent_id in the children and distinguish the type of the children
through a Child.type field).
2010/10/29 Conor <conor.edw...@gmail.com>:
> On 10/29/2010 11:51 AM, Hector Blanco wrote:
>
> Thanks Conor!
>
> The callable works like a charm! It's great news! (I've been trying to
> figure out this for 3 days... yeah... I guess I'm not that smart)
>
> Now that I have it working, a “design” question pops up. Nothing
> technical, really.
>
> As Connor mentioned in his reply:
> “Usually we consider the table with the foreign key as the "child"
> table, but that's just being picky”
>
> That's very true, and now I don't know how to design it...
>
> It would be easier to design it if we had more concrete names instead of
> "Parent" and "Child". What is the actual use case? Is this a tree hierarchy?
> Does each parent have exactly two children?
>
> I can do it the way I asked or...
>
> class Child(rdb.Model):
> rdb.metadata(metadata)
> rdb.tablename("children_table")
> id = Column("id", Integer, primary_key=True)
> parent_id = Column("id", Integer, ForeignKey(“parent_table.id”)) # New!
> type = Column("type", ShortInteger) # New!
>
> field1 = Column(“field1”, String(64)) #Irrelevant
> def __init__(self):
> self.field1 = “hello world”
>
> class Parent(rdb.Model):
> rdb.metadata(metadata)
> rdb.tablename("parent_table”)
>
> id = Column("id", Integer, primary_key=True)
>
> child1 = relationship(
> # Well... this I still don't know how to write it down,
> # but it would be something like:
> # Give me all the children whose “parent_id” is my “id”
> # AND type == 1
> # I'll deal with the joins and that depending on your answer, guys
> )
>
> child2 = relationship(
> # Would be same as above
> # AND type == 2
> )
>
> This may be good for adding new children to the parent class... If I
> add a “Parent.child3”, I just need to create a new relationship very
> similar to the already existing ones.
>
> The way I asked in my former question would imply creating a new
> relationship AND adding a new foreign key to the parent.
>
> I'd like to know what people that know much more about databases think :)
>
> I'm confused as to why you would want separate "child1", "child2", etc.
> relationships instead of a single "children" relationship. Is Child.type
> really something you want for distinguishing children, or is it something
> you added to try and make the relationships work?
>
> Assuming you really do want to keep separate "child1" and "child2"
> relationships, and they are both one-to-one relationships, they would look
> like this:
>
> # omit uselist=False if this is a one-to-many relationship
> child1 = relationship(
> "Child",
> primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 1),
> uselist=False)
> child2 = relationship(
> "Child",
> primaryjoin=lambda: and_(Child.parent_id == Parent.id, Child.type == 2),
> uselist=False)
>
> -Conor
>