Self mapping relationships

[njw]

Part of the application I am working on needs to show how a table of people are linked.

E.g.

Person A links to persons B,C and F
Person B links to persons A, C and D
Person C links to A and B
Person E doesn't link to anyone

I can see how to do this by having a links table that then relates on to the person table, but that isn't an elegant solution as the sue couls only see user names by clicking on the link name and then a tab in that link. I'd like to have a tab on the person form that displays the names of the people who are linked to that record along with the reason for the link.

E.g. for Andrew Manton

Name	Link
Fred Bloggs	Part of project QWERTY
John Smith	Part of project ASDFG
Kerry Parker	Contact of Andrew Manton
Simon Smale	Part of project QWERTY

I've tried various ways to achieve this - and if I had MySQL 4.1+ I am sure I could achieve it using an EXIST clause - but I only have access to MySQL 4.0.3 on my server.

Any ideas?

Neil

[shannah]

What about a many-to-many relationship?

In the relationships.ini file, you could have :

Code: Select all

[Links]
__sql__ = "SELECT * FROM People p inner join Links l on p.PersonID = l.LinkedPerson where l.SourcePerson = '$PersonID'



where your links table would be something like:
SourcePerson INT(11)
LinkedPerson INT(11)
Reason VARCHAR(128)

This would result in a "Links" tab in the details form for each Person whose listing would be similar to the example you used above.

[njw]

I thought I'd tried that but I probably haven't!

I'll let you know.

Neil

[njw]

Shouldn't have been so quick. That won't solve the problem because although it would show the linked person in the source person's tab, it wouldn't show the source person in the linked person's tab. However, if I do a UNION of your SQL with a similar bit of SQL that reverses SourcePerson and LinkedPerson that would do the trick wouldn't it?

[shannah]

Yes, this would also suffice for making the relationship a TWO-WAY relationship.

It occurs to me that I have never tried out UNION with relationships. Not sure if the SQL parser will have trouble with it or not. Only one way to find out though.

An alternative to using a UNION command would be something like this:

Code: Select all

__sql__ = "SELECT * FROM People p inner join Links l on (p.PersonID = l.LinkedPerson or p.PersonID=l.SourcePerson) where l.SourcePerson = '$PersonID' or l.LinkedPerson = '$PersonID'


[shannah]

OK.. after looking into it.. this is a more than just a simple fix. The relationship machinery in Dataface will require some overhauling to be able to handle self-mapping relationships (i.e. sibling relationships).

I'll add this to the todo list. This is a very important TODO, but it looks like it will be a little difficult.

[njw]

Thanks Steve. I'm glad it wasn't just me missing something!

[null]

Hi Steve - any further thoughts on this? I'm in a similar situation - a table with entries that need to relate to each other.

The workaround I've got is to treat it as a unidirectional relationship in DF, but then duplicate the relationship in the opposite direction using the after_action_new_related_record() and after_action_existing_related_record() hooks. Now, every relationship has both Source -> Link and Link -> Source entries, and the __sql__ relationship query is unidirectional & everything parses/displays the way it should.

However, this isn't the most elegant solution, to be sure. Is there an accepted DF approach? Thanks!

[shannah]

If you can give me more specifics about your relationships (i.e. tables involved and how they are related) I might be able to offer some advice.

[null]

Thanks, Steve - greatly appreciate your help! I've got two tables:

CREATE TABLE `people` (
`person_id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
PRIMARY KEY `person_id` (`person_id`)
);

CREATE TABLE `relations` (
`source_id` int(10) unsigned NOT NULL,
`link_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`source_id`,`link_id`)
);

The problem is that some people entries will need to relate to other people entries - what you called a sibling relationship. Let's say that I've got three people:

Code: Select all

person_id         name
---
1                 Joe
2                 Mary
3                 Oscar


What I'd like is to have a relation tab in Xataface that will show me Mary, if I'm in Joe's record, or will show me Joe, if I'm in Mary's record. It's a many-to-many relationship that's feeding back on itself.

Usually I'd just create the relationship table:

Code: Select all

source_id          link_id
---
1                  2


and use a query like:

SELECT people.* FROM people p INNER JOIN relations r ON (p.person_id = r.source_id OR p.person_id = r.link_id) WHERE r.source_id = '$person_id' OR r.link_id = '$person_id';

However, Xataface doesn't seem to be happy with putting such a query in the __sql__ relationships.ini. That intuitively makes sense to me (though haven't really thought it through) - it's just too ambiguous for adds/removes.

So my workaround was to add hooks to after_action_new_related_record() et al that would just create a reciprocal relationship:

Code: Select all

source_id          link_id
---
1                  2
2                  1


Now I can just use this for __sql__:

SELECT people.* FROM people p INNER JOIN relations r ON p.person_id = r.source_id WHERE r.link_id = '$person_id';

which Xataface seems to have no problems using. Displays, adds, etc. are all fine. I did need to insert:

Code: Select all

import('Dataface/Utilities.php');
Dataface_Utilities::fireEvent('before_action_remove_related_record');


at line 63 of remove_related_record.php (right before the "$form->process(array(&$form, 'delete'), true)") and then add a hook to delete the reciprocal relationship.

Like I said, it's not ideal, but it seems to be working okay so far. My question is really:

1) Have I missed something huge about how I *should* be structuring this? and

2) If not, is there a better method for implementing sibling relationships within Xataface?

Thanks again for your help on this. Please feel free to blow me off if this is too complicated. It seems to be working so far, so this is a sanity check & curiosity. Thanks!

[shannah]

Your solution seems pretty good, given the conditions. Another solution that would eliminate the redundant entries in your links table would be to have 2 relationships:
1. With your correct SQL query that Xataface doesn't like.
2. A one-way relationship that Xataface can actually add records to.

You would make the 2nd relationship invisible with the action:condition=0 in the relationship definition. Then you would have to adjust the new and existing related records actions to link to the first relationship's add/remove forms. Of course, you'd also need to redirect all requests for the list view of the 2nd relationship to point to the 1st relationship.

I suppose this solution is a little more complex than yours, but it does achieve no redundancy in the link table.

Another idea might be to use a view for the link table that creates the redundancy on the fly, but I haven't tried that solution before so there may be some pitfalls you run into there.

-Steve

[null]

Thanks, Steve - good ideas, both. I'll have to think about it some. I am indeed worried that the reciprocal relationship is a bit fragile.

To use a view - I can't think of a way to create a compound view that's updatable. A shame - aside from that, it'd be a pretty elegant solution. But if I replace the add/remove routines & redirect to the underlying table, the rest should all just work, I think.

I'll poke around some & see what I can find. Thanks again - really appreciate your help & time!

[shannah]

In thinking about this, it can almost all be done with some simple redirects.

e.g.
1. Create your 2 relationships. A which gives the correct related list, and B which allows you to add/remove records correctly.
2. In the beforeHandleRequest() method of your application delegate class:

Code: Select all

function beforeHandleRequest(){
    $app =& Dataface_Application::getInstance();
    $query =& $app->getQuery();
    if ( $query['-table'] == 'thesourcetable' ){
        if ( $query['-relationship'] == 'A' ){
            switch ($query['-action']){
                case 'new_related_record':
                case 'existing_related_record':
                case 'remove_related_record':
                    $newQuery = $query;
                    $newQuery['-relationship'] = 'B';
                    header("Location: ".$app->url($newQuery));
                    exit;
             }
        } else if ( $query['-relationship'] == 'B' ){
            if ( $query['-action'] == 'related_records_list' ){
                $newQuery = $query;
                $newQuery['-relationship'] = 'A';
                header('Location: ".$app->url($newQuery));
                exit;
            }
        }
}


Code not tested.

[null]

Got you - that *is* pretty slick. I'll give it a shot & will let you know how it works. Thanks!