Self-Referential Many-To-Many Relationships

A place to discuss development of the Xataface core.

Self-Referential Many-To-Many Relationships

Postby ADobkin » Wed Mar 14, 2012 9:11 am

Does Xataface support the scenario where a many-to-many join table is used to relate records back to other records in that same table? I just tried to set this up using the standard syntax in relationships.ini. By default, the "add existing" link didn't show up, so I added it with actions:addexisting = true. When I click on it, Xataface reports: There are no records that can be added to this relationship.

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: Self-Referential Many-To-Many Relationships

Postby shannah » Wed Mar 14, 2012 10:17 am

It's flaky when referring to itself. Much of the parsing of SQL queries in relationships assumes that it is going from one table to another.

This would be quite a difficult thing to change.
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Self-Referential Many-To-Many Relationships

Postby ADobkin » Wed Mar 14, 2012 10:45 am

Okay, thanks. I can live without it for now, but here are several common examples where something like this may be needed. Do you have any suggestions for a work around?

  • Related family members or co-workers (employee-manager) in an address book
  • Friends in a social networking application
  • Pre-requisite courses in a curriculum
  • Bordering countries, states, provinces, etc.
  • Referencing existing tickets or work orders a help desk or service management application
  • Related bug reports in a bug tracking application

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: Self-Referential Many-To-Many Relationships

Postby shannah » Wed Mar 14, 2012 11:10 am

For many-to-many relationships you could model it as a one-to-many relationship on the join table. Then you just use the lookup widget for the "id" field. Therefore adding an existing related record to the relationship is done by just adding a "new" related record.

(Note: Read-only relationships do work with to-self relationships.... the problems don't come until you need to add records.....)

This would work for all of the examples you cited here.

It wouldn't work for cases where it is already a one-to-many (e.g. parent-child).

Another workaround that would work in all cases is to create a view of the table and use that as the target of the relationship... but this creates some other work for you to integrate it properly.

-Steve
shannah
 
Posts: 4457
Joined: Wed Dec 31, 1969 5:00 pm

Re: Self-Referential Many-To-Many Relationships

Postby ADobkin » Wed Mar 14, 2012 12:30 pm

That is very helpful, but I am still having trouble with the read-only part of the relationship. Earlier, I manually inserted a few records to the join table, but they still don't show up in the related records tab. (No records matched your request.) My relationships.ini file looks like this:

Code: Select all
tickets.ticket_ID = tickets_tickets.ticket2_ID
tickets_tickets.ticket1_ID = "$ticket_ID"


BTW, does the order of the parameters matter in the first equation? In other words, would this be the same as above?

Code: Select all
tickets_tickets.ticket2_ID = tickets.ticket_ID
tickets_tickets.ticket1_ID = "$ticket_ID"
Last edited by ADobkin on Thu Mar 15, 2012 4:15 am, edited 1 time in total.
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: Self-Referential Many-To-Many Relationships

Postby ADobkin » Wed Mar 14, 2012 1:55 pm

After testing further, it looks like it is working when I use the __sql__ method to define the relationship, i.e.:

Code: Select all
__sql__ = "SELECT * FROM tickets_tickets tt, tickets t WHERE t.ticket_ID = tt.ticket2_ID AND tt.ticket1_ID = '$ticket_ID'"


However, it does not seem to work at all with the INI file syntax.

One thing I noticed though, which may be part of the flakiness you mentioned, is that when I click on one of these related records, it displays the join record rather than the other side of the relationship.
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA

Re: Self-Referential Many-To-Many Relationships

Postby ADobkin » Thu Mar 15, 2012 5:37 am

Just wanted to add that your suggestion for the one-to-many relationship on the join table is an excellent workaround. I will use that instead for now. I may also explore the view option in the future if more functionality is needed.

Thanks,
Alan
ADobkin
 
Posts: 195
Joined: Mon Oct 22, 2007 7:31 pm
Location: Atlanta, GA, USA


Return to Xataface Developers

Who is online

Users browsing this forum: No registered users and 2 guests

Powered by Dataface
© 2005-2007 Steve Hannah All rights reserved