= 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 [wiki:Documentation/1.3/ManyToManyRelationships 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.

{{{
#!xml
<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):

{{{
#!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
<?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
  1. 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
<?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 [wiki:Documentation/1.3/ManyToManyRelationships 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 [wiki:Documentation/1.3/Criteria]).

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

{{{
#!php
<?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:

{{{
#!sql
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:

{{{
#!xml
<!-- 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.

{{{
#!xml
<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.