I have a special problem with a many-to-many relationship representation I spent a few hours on now wihout a solution. This is the situation:
1. There are books.
2. Each book can have several "contributors".
3. When assigning a "contributor" to a book, a certain role is applied to that relationship. (E.g. a "contributor" can be an author or co-author or an editor or a publisher or an illustrator etc.)
4. A contributor may be assigned more than one time to a book, with different roles.
5. A contributor can be a contributor to several books, with same or different roles.
My database tables are:
1. books (main table):
book_id
book_title
2. contributors:
contributor_id
contributor_name
3. roles:
role_id
role_description
To maintain the many-to-many relationships here, I set up a 4th table:
4. bo_co_ro:
bo_co_ro_id
book_id
contributor_id
role_id
What I want to get in Xataface is:
1. When I create or edit a book I want to assign one or several (existing) contributors to that book and assign each contributor at the same time an (existing) role.
2. I want to be able to change the role of an already assigned contributor in the book editing context without having to remove the book-contributor-relation first and then assigning it anew.
What I tried so far is:
1. In relationships.ini of the books table I stated:
- Code: Select all
[Contributors]
__sql__ = "SELECT * FROM bo_co_ro bcr INNER JOIN contributors c ON bcr.contributor_id = c.id WHERE bcr.book_id = '$book_id'"
That gave me access to the contributors and created a select field in the interface.
To be able to select a role as well I did this:
2. In fields.ini of the bo_co_ro table I stated:
- Code: Select all
[role_id]
widget:type = select
vocabulary = roles
and
3. in valuelists.ini of the bo_co_ro table I stated:
- Code: Select all
[roles]
__sql__ = "SELECT role_id, role_description FROM roles ORDER BY role_description"
That indeed displayed a select field with the respective role_description values to choose from.
With these settings I experience the following behaviour when I click on "add existing contributor record":
1. When I select a contributor and a role and then click on the Save button, I get this message:
Fatal error: [pear_error: message="No relationship specified." code=0 mode=return level=notice prefix="" info=""] in X:\xxxxxxxx\xataface\Dataface\Application.php on line 1607
2. A relation has been saved nonetheless, but not with the selected contributor - always with the first record value of table contributors instead, regardless of what I selected. However, the role is stored correctly.
What am I doing wrong here?
Thanks,
Steve