Datamapper ORM Multi-tabel Relationship Halp!

[eluser]bcdennis[/eluser]
From the doc:

Occasionally however, you might have a need to define a relationship between more than two tables. In this case, you will have to create a joined table with more than two foreign keys, one to each of the tables involved in the relationship. In this situation Datamapper can no longer automatically generate the name of the joined table. Instead, you will have to use the join_table to manually define the name of the joined table.

I'm looking for how to configure and use the datamapper for a multi relationship like this (just an example, please ignore any flaws in the data design).

A book has many chapters. A chapter belongs to one book. Each chapter has many pages. A page can be in multiple chapters. Assume each is it's own table. How do set the join table up, and how do I setup the has_manys and has_one's for each class?

I'm guessing the join table would look like this:

Code:

books_chapters_pages
*******************
[id][book_id][chapter_id][page_id]

I'm guessing the has_many's would look like this:

Code:

class Book ...
{
   var $has_many = array(
       "chapter" => array("join_table" => "books_chapters_pages"));

...

class Chapter ...
{
   var $has_one = array(
       "book" => array("join_table" => "books_chapters_pages"));
   var $has_many = array(
       "page" => array("join_table" => "books_chapters_pages"));

...

class Page ...
{
   var $has_many = array(
       "chapter" => array("join_table" => "books_chapters_pages"));

...

Expected usage:

Code:

$book = new Book();
$book->get_by_title("Sample Book 1");

$book->chapter->get_by_title("Chapter 1");

$book->chapter->page->get();

And the pages retrieved are what belongs to that chapter which belongs to that book.

That doesn't work. Has anyone done multi-table relationships that can show me how to properly setup the tables, the relationships and give me some examples of usage?

Thanks!

[eluser]WanWizard[/eluser]
I would say:

books -> has_many -> chapters
chapters -> has_one -> books
chapters -> has_many -> pages
pages -> has_many -> chapters

This is straitforward Datamapper, no need to create such a relationship, as there is no direct relation between books and pages. That means no complicated join tables, just use

Code:

class Book extends Datamapper {
    $has_many = array('chapter');
}

class Chapter extends Datamapper {
    $has_one = array('book');
    $has_many = array('page');
}

class Page extends Datamapper {
    $has_many = array('chapter');
}

This will require a book_id in the chapters table, and a relationship table called chapters_pages, containing chapter_id and page_id, to create the many-to-many relation between the two.

With a setup like this, your expected usage just works.

[eluser]Genki1[/eluser]
Hi WanWizard,

This is a good post for illustrating relationships. I have a slight variation and wonder, do I need a custom join table?

Example scenario: a User can purchase many website Templates. Each Template can be used by many Users. Each User specifies many configuration Options for each of their Templates.

User -> has many -> Templates
Templates -> has many -> Users
Templates -> has many -> Options
Options -> has one -> User
Options -> has one -> Template

Desired usage: Show me all of the Options for this User's Template.

In order to connect all three tables, do I need to use a multi-relationship join table like this:

join table: "users_templates_options" (per DM guidelines, the names would be in alphabetical order)

Code:

[id] [user_id] [template_id] [option_id] [value_for_this_template_option]
  1       1         1             1        Some data
  2       1         1             3        more stuff
  3       1         2             1        Maybe my name here
  4       1         2             5        contact info
  5       2         1             1        Yet more user data

[eluser]WanWizard[/eluser]
Datamapper doesn't support multi-relationship joins.

Datamappers expects the combination of the two foreign keys in a relationship table to be unique. If not, you'll get duplicate results when you query that relation.

I suggest you create this table as a separate table like in your example (lets call it purchases). Give it has_one's to user, template and option (and these three a has_many to this table), and use the value a normal data column.

So

User -> has many -> Purchases
Templates -> has many -> Purchases
Options -> has many -> Purchases
Purchases -> has one -> User
Purchases -> has one -> Template
Purchases -> has one -> User

You shouldn't have any problem with the desired usage:

Code:

$user = new User(1);

// get all this user's purchases
$user->purchase->get();

// get info about a specific purchase
$user->purchase->where('template_id', 16)->get();

// get all users that bought an option
$purchase = new Purchase();
$purchase->include_related('user')->where('option_id', 66)->get();

[eluser]Genki1[/eluser]
Okay, I get it that multi-relationships are not supported and I'm thinking about your answer. However, your answer changed my scenario and I want to make sure I implement it correctly (to avoid banging my head on the table for another 5 hours :-) )

What I want is this: (item in red is an edit to your answer, above)

User -> has many -> Purchases
Templates -> has many -> Purchases
Options -> has many -> Purchases
Purchases -> has one -> User
Purchases -> has one -> Template
Purchases -> has many -> Options

So, will this work...

Code:

$user = new User(1);

// get all this user's purchases
$user->purchase->get();

// get info about a specific purchase
$user->purchase->where('template_id', 16)->get();

// get all options for this purchase
$purchase = new Purchase(3);
$purchase->option->get();

...using this table?

join_table: "purchases"

Code:

[id] [user_id] [template_id] [option_id] [value_for_this_template_option]
  1       1         1             1        Some data
  2       1         1             3        more stuff
  3       1         2             1        Maybe my name here
  4       1         2             5        contact info
  5       2         1             1        Yet more user data

[eluser]WanWizard[/eluser]
Sorry about the typo.

No, Purchases -> has_one -> Options. Not has_many, you're using an in-table FK, so one purchase record can only point to one option record.

If you want to know all options for the purchase of a specific template, use

Code:

$user = new User(1);
$template = new Template(16);

// get the details of the purchase of template 16 by user 1
$purchase = new Purchase();
$purchase->where_related($user)->where_related($template)->option->get();

[eluser]Genki1[/eluser]
Your idea of Purchases "has one" Option is throwing me off because there are multiple Options applied to each Template. Maybe my scenario is not clear. Here's what I'm doing:

A User purchases a Template.
There is a library of 15 configuration settings (I called them "Options" in my posting here).
Several of the config settings are automatically applied to the Template and are set with a default value which the user can change. (Note: user purchases only the Template, not the config settings/options.)

Example of two purchases:

User "Joe" purchases the "Blue Sky" template. Ten of the 15 config settings are automatically applied to the template and each setting is loaded with a default value.

User "Harro" purchases the "Gentle Valley" template. Twelve of the 15 config settings are automatically applied to this template and loaded with default values.

Code:

User: Joe
Template: Blue Sky
Config settings:
[option_id] [option_name]     [value]
  1           Font              Arial
  2           Background color  Blue
  3           State             California

Code:

User: Harro
Template: Gentle Valley
Config settings:
[option_id] [option_name]     [value]
  1           Font              Helvetica
  3           State             Hawaii
  5           Position          Center
  6           Foreground image  Sunflowers

So, as in my earlier post, I envision my "Purchases" table looking like this:

Code:

[id] [user_id] [template_id] [option_id] [value]
  1       1         1             1        Arial
  2       1         1             2        Blue
  3       1         1             3        California
  4       2         7             1        Helvetica
  5       2         7             3        Hawaii
  6       2         7             5        Center
  7       2         7             6        Sunflowers

Based on this, how can Purchases be "has_one" to Options?

[eluser]WanWizard[/eluser]
Ok. I misunderstood you.

In that case you need a many-to-many between Purchase and Option, and a relationship table called options_purchases, with 'id', 'option_id' and 'purchase_id'.

And you should store the value in that relationship table (as a join_field), since it's a property of the relation, and not of the purchase.

Code:

the purchases table:
[id] [user_id] [template_id]
  1       1         1
  2       2         7

the options_purchases table:
[id] [purchase_id] [option_id] [value]
  1        1            1        Arial
  2        1            2        Blue
  3        1            3        California
  4        2            1        Helvetica
  5        2            3        Hawaii
  6        2            5        Center
  7        2            6        Sunflowers

You can than access the purchases of a user by using

Code:

// load a user
$user = new User(1);

// get all the users purchases
$user->purchase->get();

// loop through the purchases
foreach ($user->purchase as $purchase)
{
    // get the template purchased
    $purchase->template->get();

    // get all purchased options including the option value
    $purchase->option->include_join_fields()->get();
}

[eluser]Genki1[/eluser]
Excellent, that makes perfect sense. Thank you very much for taking the time to explain this to me, maintaining DM and sharing your expertise.