Self-mapping relationships
Posted: Fri Oct 26, 2012 7:33 amMy database has two type of self-mapping relationships: a) 1-to-1 and b) many-to-many.
An example for a) is a table 'server' with (amongst others) fields 'id' and 'cluster_partner_id'. If one of the server-records in this table is part of a high-availability cluster, the 'cluster_partner_id' points to his cluster-partner in the same 'server' table. In the relationships.ini file of the 'server' table, I made the following entry:
[HA_Partner]
server.id = "$cluster_partner_id"
Problem here: As soon as I add the above relationship, the view details/edit/new record pages don't load anymore (blank page in browser). As soon as I comment out the 2 lines above, the pages work ok again (there are more relationships to other tables).
An example for b) is a table 'component'. The records within this table are components of a bigger setup, which depend on each other, by providing and consuming services from each other. One component can provide its service to multiple others and can consume services from multiple others, hence the many-to-many relationship. (A similar topic would probably be relationships between persons.) To implement the many-to-many relationship, I have a 'comp_comp' table with 4 fields (id, consuming_comp_id, providing_comp_id, dependency_strength). In the relationships.ini file of the 'component' table, I made the following entries:
[Consuming_Components]
component.id = comp_comp.consuming_comp_id
comp_comp.providing_comp_id = "$id"
[Providing_Components]
component.id = comp_comp.providing_comp_id
comp_comp.consuming_comp_id = "$id"
(Alternatively, this could probably be implemented with only one relationship and an additional field 'direction' on the 'comp_comp' table, but I wanted a clear separation between consumer-relationships and provider-relationships. Anyway, this would not influence the problem described below.)
Problem here: The behavior is not as expected, e.g. existing self-relations are not displayed, and new ones cannot be made. (I also have many-to-many relationships to other tables, which are defined with the same mechanism as above and they all work ok.)
Both of the above approaches work well and as expected in direct sql-queries, i.e. the database logic is ok, but Xataface seems to have a hard time with self-mapping relationships.
I saw a thread with a similar topic (http://xataface.com/forum/viewtopic.php?t=3787#23803), but it is from 2006/2009, so I was wondering if there have been changes to Xataface in the meantime that allow to handle such self-mapping relationships. Or maybe someone here had the same problem and can share some experience and ideas.
Thanks in advance for any feedback,
Christoph
An example for a) is a table 'server' with (amongst others) fields 'id' and 'cluster_partner_id'. If one of the server-records in this table is part of a high-availability cluster, the 'cluster_partner_id' points to his cluster-partner in the same 'server' table. In the relationships.ini file of the 'server' table, I made the following entry:
[HA_Partner]
server.id = "$cluster_partner_id"
Problem here: As soon as I add the above relationship, the view details/edit/new record pages don't load anymore (blank page in browser). As soon as I comment out the 2 lines above, the pages work ok again (there are more relationships to other tables).
An example for b) is a table 'component'. The records within this table are components of a bigger setup, which depend on each other, by providing and consuming services from each other. One component can provide its service to multiple others and can consume services from multiple others, hence the many-to-many relationship. (A similar topic would probably be relationships between persons.) To implement the many-to-many relationship, I have a 'comp_comp' table with 4 fields (id, consuming_comp_id, providing_comp_id, dependency_strength). In the relationships.ini file of the 'component' table, I made the following entries:
[Consuming_Components]
component.id = comp_comp.consuming_comp_id
comp_comp.providing_comp_id = "$id"
[Providing_Components]
component.id = comp_comp.providing_comp_id
comp_comp.consuming_comp_id = "$id"
(Alternatively, this could probably be implemented with only one relationship and an additional field 'direction' on the 'comp_comp' table, but I wanted a clear separation between consumer-relationships and provider-relationships. Anyway, this would not influence the problem described below.)
Problem here: The behavior is not as expected, e.g. existing self-relations are not displayed, and new ones cannot be made. (I also have many-to-many relationships to other tables, which are defined with the same mechanism as above and they all work ok.)
Both of the above approaches work well and as expected in direct sql-queries, i.e. the database logic is ok, but Xataface seems to have a hard time with self-mapping relationships.
I saw a thread with a similar topic (http://xataface.com/forum/viewtopic.php?t=3787#23803), but it is from 2006/2009, so I was wondering if there have been changes to Xataface in the meantime that allow to handle such self-mapping relationships. Or maybe someone here had the same problem and can share some experience and ideas.
Thanks in advance for any feedback,
Christoph