nested relationships

Archived from the Xataface Users forum.

Steve — Fri Sep 17, 2010 8:29 am

Hi,

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:

  1. 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:

  1. In fields.ini of the bo_co_ro table I stated:
Code: Select all
[role_id] widget:type = select vocabulary = roles

and

  1. 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

shannah — Fri Sep 17, 2010 11:20 am

Hi Steve,

What version of Xataface are you using. That will help so I can check out line 1607 and see what’s there.

-Steve

Steve — Fri Sep 17, 2010 10:21 pm

Hi Steve, I use version 1.2.4 1811. Thanks!

Steve — Fri Oct 15, 2010 9:59 pm

Never mind, meanwhile I successfully completed my application by coding the relevant parts from scratch. Gives me better control and I know exactly what happens in any given moment. Of course, this was rather time-consuming. However, thanks for creating Xataface which is a great tool for not so complicated tasks as happen to occur in my project.