Location: PHPKode > projects > DIY Blog > diy-blog/lib/propel/docs/en/user_guide/book/chapters/Relationships.html
-File         $Id: Relationships.html,v 1.1 2004/07/08 01:17:06 hlellelid Exp $
-License      GNU FDL (http://www.gnu.org/copyleft/fdl.html)
-Copyright    2003, Propel project
-Author       Hans Lellelid, hide@address.com
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/2000/REC-xhtml1-20000126/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
  <title>Propel Guide</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"/>
  <link rel="Stylesheet" rev="Stylesheet" href="../css/Documentation.css" type="text/css" charset="iso-8859-1"/>


<p>Propel supports basic one-to-one relationships. More complex many-to-many relationships 
  are also possible by defining the cross-reference tables in your data model 
  and explicitly using this cross-reference table when selecting records. This 
  chapter describes how to create inter-related entities and use optimized methods 
  for returning related entities.</p>
<h2><a name="Relationships.Defining"></a>Defining Entity Relationships</h2>
<p>The Propel representation of entity relationships corresponds very closely 
  to the way relationships are represented at the database level: namely through 
  foreign keys. In the data model XML, you can use the <strong>&lt;foreign-key&gt;</strong> 
  tag to specify a column as a foreign key.</p>
<pre title="example fkey schema">&lt;table name=&quot;book&quot;&gt;
 &lt;column name=&quot;book_id&quot; type=&quot;INTEGER&quot; required=&quot;true&quot; primaryKey=&quot;true&quot;/&gt;
 &lt;column name=&quot;title&quot; type=&quot;VARCHAR&quot; size=&quot;100&quot; required=&quot;true&quot;/&gt;
 &lt;column name=&quot;author_id&quot; type=&quot;INTEGER&quot; required=&quot;true&quot;/&gt;
 &lt;foreign-key foreignTable=&quot;author&quot;&gt;
&lt;table name=&quot;author&quot;&gt;
 &lt;column name=&quot;author_id&quot; type=&quot;INTEGER&quot; required=&quot;true&quot; primaryKey=&quot;true&quot;/&gt;
 &lt;column name=&quot;fullname&quot; type=&quot;VARCHAR&quot; size=&quot;40&quot; required=&quot;true&quot;/&gt;
<p>Propel will generate SQL definitions that use native foreign keys if your database 
  driver supports them. Propel will also use foreign key information to generate 
  methods in your peer and object classes to fetch related objects.</p>
<h2><a name="Relationships.FetchingRelated"></a>Fetching Related Objects</h2>
<p>Using the example above (based on provided bookstore schema), you would have 
  a <strong>Book-&gt;getAuthor()</strong> that would return an Author object using 
  the specified foreign key.</p>
<pre title="example fetching fkey object">$books = BookPeer::doSelect(new Criteria());
foreach($books as $book) {
 $author = $book-&gt;getAuthor();
<p>The above code would result in the execution of two SQL statements:</p>
  <li>SELECT * FROM book</li>
  <li>SELECT * FROM author WHERE author_id = $book-&gt;getAuthorId()</li>
<p>While clearly this method works, it is not optimal -- especially if your database 
  has native support for foreign keys. Propel also generates methods in your base 
  peer class to fetch both book and author information in a single query. </p>
<pre title="fetch fkey sql">$books = BookPeer::doSelectJoinAuthor(new Criteria());
foreach($books as $book) {
 $author = $book-&gt;getAuthor();
<p>In the above case only a single query is performed:</p>
  <li>SELECT * FROM book INNER JOIN author ON author.author_id = book.author_id</li>
<p>Note: in order to limit the methods in the public API, <em>the join methods 
  are protected methods of the base peer class</em>; in order to use them you 
  must create public methods in your stub peer class that invoke the protected 
  parent methods.</p>
<pre title="example of invoking protected join method from stub class">class BookPeer {
 public function doSelectJoinAuthor(Criteria $c) {
  return parent::doSelectJoinAuthor($c);

<h2><a name="Relationships.ManyToMany"></a>Many-to-Many Relationships</h2>
<p>As mentioned in the chapter introduction, Propel's support for many-to-many 
  relationships involves a middle step: defining the cross-reference table in 
  your data model, and using results to perform lookups. </p>
<p>Take for example, a need to relate books with the people reading them -- many 
  people reading a single book, one person reading many books:</p>
<pre title="example of invoking protected join method from stub class">&lt;table name=&quot;book_reader_ref&quot;&gt;
 &lt;column name=&quot;book_id&quot; type=&quot;INTEGER&quot; required=&quot;true&quot; primaryKey=&quot;true&quot;/&gt;
 &lt;column name=&quot;reader_id&quot; type=&quot;INTEGER&quot; required=&quot;true&quot; primaryKey=&quot;true&quot;/&gt;
 &lt;foreign-key foreignTable=&quot;book&quot;&gt;
 &lt;foreign-key foreignTable=&quot;reader&quot;&gt;
<p>In your PHP script you will need to make use of the &quot;middleman&quot; cross-reference 
  table to retrieve the related entities:</p>
<pre title="Using middleman class from PHP">$books = BookPeer::doSelect(new Criteria());

// for every book get all readers
foreach($books as $book) {
 $readers = $book-&gt;getBookReaderRefsJoinReader();
<p>The code above will execute two SQL statements:</p>
  <li>SELECT * FROM book</li>
  <li>SELECT * FROM book_reader_ref INNER JOIN reader ON reader.reader_id = book_reader_ref.reader_id 
    WHERE book_reader_ref.book_id = $book-&gt;getBookId()</li>
<p>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 the support for one-to-one joins. 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). </p>
<h2><a name="Relationships.CascadingDelete"></a>Cascading Delete</h2>
<p>Propel also supports cascading deletes, which can be specified using the <strong>onDelete=&quot;cascade&quot;</strong> 
  option of the <strong>&lt;foreign-key&gt;</strong> tag. Torque also supported 
  an onUpdate=&quot;cascade&quot; option, but since Propel does not allow updates 
  to include changes to the primary key it would never actually be invoked -- 
  and some database simply don't support this. Propel provides a new cascading 
  delete emulation for databases that don't support this trigger (e.g. MySQL).</p>
<pre title="Example of cascade delete definition">&lt;table name=&quot;review&quot;&gt;
 &lt;column name=&quot;review_id&quot; type=&quot;INTEGER&quot; primaryKey=&quot;true&quot; required=&quot;true&quot;/&gt;
 &lt;column name=&quot;reviewer&quot; type=&quot;VARCHAR&quot; size=&quot;50&quot; required=&quot;true&quot;/&gt;
 &lt;column name=&quot;book_id&quot; required=&quot;true&quot; type=&quot;INTEGER&quot;/&gt;<br />	&lt;foreign-key foreignTable=&quot;book&quot; onDelete=&quot;CASCADE&quot;&gt;<br />   &lt;reference local=&quot;book_id&quot; foreign=&quot;book_id&quot;/&gt;<br /> &lt;/foreign-key&gt;	  <br />&lt;/table&gt;</pre>
<p>In the example above, the review rows will be automatically removed if the 
  related book is deleted.</p>
Return current item: DIY Blog