Table question

[sophistry]

For anyone familiar with tables (MySQL) ...

I'm trying to set up a many-to-many relationship within the same table. Basically I have record 'related' to each other. I'd like to show, in the record view page, which other records are related to the one you are viewing, and be able to click over to them. The records are not distinct enough to have their own tables, but we will sometimes use a record as a reference for the development of another record.

As an example, let's say we have a table of Movie titles. You want to have a tab for related movies ...

The join table fields could be related_ID, from_ID, to_ID

If we have two movies in a table called Movies...

movie_ID, name
1, Back to the Future
2, Back to the Future 2

And a join table called MovieRelated looks like:

related_ID, from_ID, to_ID
1, 1, 2

We add a relationship to the tables/Movies/relationships.ini ...
[Related Movies]
Movies.movie_ID = MovieRelated.from_ID
MovieRelated.to_ID = "$movie_ID"

Should this not work? It doesn't seem to in my case. In the fields.ini and valuelists.ini, I have a query/vocabulary setup that uses a better field in place of the ID's, and it works on another table (that doesn't join back to itself). What I get on this one is a blank dropdown list.

Is there a specific way to do this, or is it possible to do it this way (this simply)? This should at least be a one-to-many relationship, but it would be even better if it worked both ways in one record.

Thanks,
Rory

[shannah]

Unfortunately a limitation of the current relationships implementation makes relationships between the same table difficult.

There are a few ugly kludges that you can do to work around this (including creating a view of your table and making the relationship go the view instead)...

-Steve

[sophistry]

Yeah, I realized it wasn't going to work for the time being, and there wasn't much information (besides a parent/child join table) out there in the MySQL community.

My current solution is basically a one-to-many relationship to the old many-to-many relational table. It's a one-way road, but at least when you add a record is gives you a dropdown list of records from the original table ... thanks to Xataface

Maybe sometime later I'll revisit it to see if I can get a real connection going ... although the absolute solution would be to have tag clouds on each record to properly group them all by related objects, but that's way far down the line from where I am now.

Thanks for the response, Steve. Kudos for your attention!

Rory