1. Defining Relationships
  2. Fetching Related Objects
  3. On-Update and On-Delete Triggers

Basic Relationships

Propel support for relationships between objects maps directly to the way that databases support relationships between tables using foreign keys. Relationship support in Propel is therefore easy to understand, but it also inherits some of the simplicity of this model; for example, Propel doesn't hide the fact that you must use a cross-reference table for many-to-many relationships (see the Many-to-Many Relationships page for more on that topic).

Defining Relationships

Relationships are specified by creating foreign keys in the related <table> sections of your schema.xml file. You use the <foreign-key> tag to create a relationship between one table and another, using the same basic principle that you would use in relating one table to another at the database level. 

<table name="book">
 <column name="book_id" type="INTEGER" required="true" primaryKey="true"/>
 <column name="title" type="VARCHAR" size="100" required="true"/>
 <column name="author_id" type="INTEGER" required="true"/>
 <foreign-key foreignTable="author">
   <reference
     local="author_id"
     foreign="author_id"/>
 </foreign-key>
</table>
<table name="author">
 <column name="author_id" type="INTEGER" required="true" primaryKey="true"/>
 <column name="fullname" type="VARCHAR" size="40" required="true"/>
</table>

Propel will use this information to:

  • Generate native FOREIGN KEY creation SQL in your DDL file (e.g. schema.sql) and
  • Create getter & setter methods in your generated objects (e.g. Book will have a getAuthor() and setAuthor() method)

Fetching Related Objects

Using the example above (based on provided bookstore schema), you would have a Book->getAuthor() that would return an Author object using the specified foreign key. 

<?php

$books = BookPeer::doSelect(new Criteria());
foreach($books as $book) {
 $author = $book->getAuthor();
}

The above code would result in the execution of 1+n SQL statements, where n is the number of books (and hence number of iterations of the foreach loop):

  1. 1 x SELECT * FROM book
  2. n x SELECT * FROM author WHERE author_id = $book->getAuthorId()

While clearly this method works, it is not optimal to execute that second query for each iteration. Propel also generates methods in your base peer class to fetch both book and author information in a single query. 

<?php

$books = BookPeer::doSelectJoinAuthor(new Criteria());
foreach($books as $book) {
 $author = $book->getAuthor();
}

In the above case only a single query is performed:

  1. SELECT * FROM book INNER JOIN author ON author.author_id = book.author_id

Continue reading the Many-to-Many Relationships page for a treatment of this more-advanced realtionship topic.

On-Update and On-Delete Triggers

Propel also supports the ON UPDATE and ON DELETE aspect of foreign keys. These properties can be specified in the <foreign-key> tag using the onUpdate and onDelete attributes. Propel supports values of 'CASCADE', 'SETNULL', and 'RESTRICT' for these attributes. For databases that have native foreign key support, these trigger events will be specified at the datbase level when the foreign keys are created. For databases that do not support foreign keys, this functionality will be emulated by Propel. 

<table name="review">
 <column name="review_id" type="INTEGER" primaryKey="true" required="true"/>
 <column name="reviewer" type="VARCHAR" size="50" required="true"/>
 <column name="book_id" required="true" type="INTEGER"/>
        <foreign-key foreignTable="book" onDelete="CASCADE">
   <reference local="book_id" foreign="book_id"/>
 </foreign-key>   
</table>

In the example above, the review rows will be automatically removed if the related book row is deleted.