Last modified 2 years ago

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)

One-to-One Relationships

Propel 1.3 supports special case one-to-one relationships (see ticket:279). One-to-one relationships are defined when the primary key is also a foreign key. For example (simplified from bookstore schema.xml): 

  <table name="bookstore_employee" description="Employees of a bookstore">
        <column
          name="id"
          type="INTEGER"
          primaryKey="true"
          autoIncrement="true"
          description="Employee ID number"/>
        <column
          name="name"
          type="VARCHAR"
          size="32"
          description="Employee name"/>
  </table>

  <table name="bookstore_employee_account" description="Bookstore employees' login credentials">
        <column
          name="employee_id"
          type="INTEGER"
          primaryKey="true"
          description="Primary key for "
          />
        <column
          name="login"
          type="VARCHAR"
          size="32"/>
        <column
          name="password"
          type="VARCHAR"
          size="100"/>
        <foreign-key foreignTable="bookstore_employee" onDelete="cascade">
          <reference local="employee_id" foreign="id"/>
        </foreign-key>
  </table>

Because the primary key of the bookstore_employee_account is also a foreign key to the bookstore_employee table, Propel interprets this as a one-to-one relationship and will generate singular methods for both sides of the relationship -- e.g. BookstoreEmployee->getBookstoreEmployeeAccount() and BookstoreEmployeeAccount->getBookstoreEmployee().

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 using a left join*. 

<?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 LEFT 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.

* Note that in Propel 1.3 the behavior changed from performing INNER JOIN to using LEFT JOIN by default. Please see wiki:Documentation/1.3/Upgrading for more information and instructions on how to keep the original INNER JOIN behavior if your application depends on this.

Using Relationships in Criteria

You can create queries that use relationships using the doSelectJoin*() methods or by explicitly specifying joins in the Criteria object (see Documentation/1.3/Criteria).

In either case, you can add expressions for any of the joined tables to the Criteria object: 

<?php

$c = new Criteria(AuthorPeer::DATABASE_NAME);

$c->addJoin(AuthorPeer::ID, BookPeer::AUTHOR_ID, Criteria::INNER_JOIN);
$c->addJoin(BookPeer::PUBLISHER_ID, PublisherPeer::ID, Criteria::INNER_JOIN);

$c->add(PublisherPeer::NAME, 'Some Name');

$authors = AuthorPeer::doSelect($c);

... would be equivalent to: 

SELECT * 
FROM author 
  INNER JOIN book ON book.author_id = author.id 
  INNER JOIN publisher ON publisher.id = book.publisher_id
WHERE publisher.name = 'Some Name'

Overriding PHP Names for Relationship Getters/Setters

As of Propel 1.3 (see ticket:23), it is possible to provide PHP names for the methods that are created for fetching related foreign key objects. You can specify foreign key phpName and refPhpName attributes in the <foreign-key> element. Hopefully an example will make the implementation (and motivation for this feature) clear: 

<!-- test self-referencing foreign keys and inheritance-->
  <table name="bookstore_employee">
        <column
          name="id"
          type="INTEGER"
          primaryKey="true"
          autoIncrement="true"
          description="Employee ID number"/>
        <column
          name="name"
          type="VARCHAR"
          size="32"
          description="Employee name"/>
        <column
          name="job_title"
          type="VARCHAR"
          size="32"
          description="Employee job title"/>
        <column
          name="supervisor_id"
          type="INTEGER"
          description="Fkey to supervisor."/>
        <foreign-key foreignTable="bookstore_employee" 
                     phpName="Supervisor" 
                     refPhpName="Subordinate">
          <reference local="supervisor_id" foreign="id"/>
        </foreign-key>
  </table>

This is an example from a self-referencing table in bookstore schema.xml.

The phpName attribute affects naming of methods like setSupervisor(), while the refPhpName attribute affects naming of methods that work with the referencing foreign keys -- e.g. addSubordinate(), getSubordinates().

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.