Many-to-Many Relationships

Because Propel's support for relationships maps directly to the way that relationships are handled at the database level, there is no magical support for many-to-many relationships in Propel -- such as the support, for example, provided by ORM layers that involve more abstraction (like  Hibernate for Java). What this means is that working with many-to-many relationships in Propel requires you to reference the lookup/cross-reference table. You can always create your own methods to do this automatically for you and put them in the stub classes. In the future there may be ways to indicate to Propel which tables are cross-reference tables -- and therefore Propel could automate some of this. But that isn't the case yet.

An Example

Take for example, a need to relate books with the people reading them -- many people reading a single book, one person reading many books:

<table name="book_reader_ref">
 <column name="book_id" type="INTEGER" required="true" primaryKey="true"/>
 <column name="reader_id" type="INTEGER" required="true" primaryKey="true"/>
 <foreign-key foreignTable="book">
   <reference
     local="book_id"
     foreign="book_id"/>
 </foreign-key>
 <foreign-key foreignTable="reader">
   <reference
     local="reader_id"
     foreign="reader_id"/>
 </foreign-key>
</table>

In your PHP script you will need to make use of the "middleman" cross-reference table to retrieve the related entities:

<?php
$books = BookPeer::doSelect(new Criteria());

// for every book get all readers
foreach($books as $book) {
  $readerRefs = $book->getBookReaderRefsJoinReader();
  foreach($readerRefs as $ref) {
    $reader = $ref->getReader(); // <-- this isn't an additional query
                                 // since we used the Join method above
  }
}

The code above will execute 1+n SQL statements, where n is the number of books rows returned:

1. SELECT * FROM book
2. n x SELECT * FROM book_reader_ref INNER JOIN reader ON reader.reader_id = book_reader_ref.reader_id WHERE book_reader_ref.book_id = $book->getBookId()

While this method is not excessively wasteful -- as performing a single select to retrieve many-to-many joined results doesn't usually make sense -- but it is also less elegant than a single method call. Requiring the explicitly reference of the cross-reference table is a drawback to using the very literal data modeling approach adopted by Propel (inherited from Torque).

A Suggestion: Creating Custom Stub Methods

To make your life easier, you may wish to create a stub method of the Book and/or Reader class. For example, we could add a getReaders() method to our Book class that would encapsulate the logic above.

Edit your Book.php stub class to add this method:

<?php
class Book extends BaseBook {
  
  /**
   * Convenience method to fetch all related Reader objects.
   * @param Criteria $c An [optional] Criteria to limit results
   * @return array Reader[]
   */
  public function getReaders($c = null) {
     $readers = array();
     foreach($book->getBookReaderRefsJoinReader($c) as $ref) {
        $readers[] = $ref->getReader();
     }
     return $readers;
  }
}