Skip to content


Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
tree: bc1ea20fe1
Fetching contributors…

Cannot retrieve contributors at this time

400 lines (311 sloc) 17.074 kb
layout title
Basic Relationships

Basic Relationships

The definition of foreign keys in your schema allows Propel to add smart methods to the generated model and query objects. In practice, these generated methods mean that you will never actually have to deal with primary and foreign keys yourself. It makes the task of dealing with relations extremely straightforward.

Inserting A Related Row

Propel creates setters for related objects that simplify the foreign key handling. You don't actually have to define a foreign key value. Instead, just set a related object, as follows:

{% highlight php %} <?php $author = new Author(); $author->setFirstName("Leo"); $author->setLastName("Tolstoy"); $author->save();

$book = new Book(); $book->setTitle("War & Peace"); // associate the $author object with the current $book $book->setAuthor($author); $book->save(); {% endhighlight %}

Propel generates the setAuthor() method based on the phpName attribute of the <foreign-key> element in the schema. When the attribute is not set, Propel uses the phpName of the related table instead.

Internally, the call to Book::setAuthor($author) translates into Book::setAuthorId($author->getId()). But you don't actually have to save a Propel object before associating it to another. In fact, Propel automatically "cascades" INSERT statements when a new object has other related objects added to it.

For one-to-many relationships - meaning, from the other side of a many-to-one relationship - the process is a little different. In the previous example, one Book has one Author, but one Author has many Books. From the Author point of view, a one-to-many relationships relates it to Book. So Propel doesn't generate an Author::setBook(), but rather an Author::addBook():

{% highlight php %} <?php $book = new Book(); $book->setTitle("War & Peace"); $book->save();

$author = new Author(); $author->setFirstName("Leo"); $author->setLastName("Tolstoy"); // associate the $book object with the current $author $author->addBook($book); $author->save(); {% endhighlight %}

The result is the same in the database - the author_id column of the book row is correctly set to the id of the author row.

Save Cascade

As a matter of fact, you don't need to save() an object before relating it. Propel knows which objects are related to each other, and is capable of saving all the unsaved objects if they are related to each other.

The following example shows how to create new Author and Publisher objects, which are then added to a new Book object; all 3 objects are saved when the Book::save() method is eventually invoked.

{% highlight php %} <?php /* initialize Propel, etc. */

$author = new Author(); $author->setFirstName("Leo"); $author->setLastName("Tolstoy"); // no need to save the author yet

$publisher = new Publisher(); $publisher->setName("Viking Press"); // no need to save the publisher yet

$book = new Book(); $book->setTitle("War & Peace"); $book->setIsbn("0140444173"); $book->setPublisher($publisher); $book->setAuthor($author); $book->save(); // saves all 3 objects! {% endhighlight %}

In practice, Propel 'cascades' the save() action to the related objects.

Reading Related Object Properties

Just like the related object setters, Propel generates a getter for every relation:

{% highlight php %} <?php $book = BookQuery::create()->findPk(1); $author = $book->getAuthor(); echo $author->getFirstName(); // 'Leo' {% endhighlight %}

Since a relationship can also be seen from the other end, Propel allows the foreign table to retrieve the related objects as well:

{% highlight php %} <?php $author = AuthorQuery::create()->findPk(1); $books = $author->getBooks(); foreach ($books as $book) { echo $book->getTitle(); } {% endhighlight %}

Notice that Propel generated a getBooks() method returning an array of Book objects, rather than a getBook() method. This is because the definition of a foreign key defines a many-to-one relationship, seen from the other end as a one-to-many relationship.

Propel also generates a countBooks() methods to get the number of related objects without hydrating all the Book objects. for performance reasons, you should prefer this method to count($author->getBooks()).

Getters for one-to-many relationship accept an optional query object. This allows you to hydrate related objects, or retrieve only a subset of the related objects, or to reorder the list of results:

{% highlight php %} <?php $query = BookQuery::create() ->orderByTitle() ->joinWith('Book.Publisher'); $books = $author->getBooks($query); {% endhighlight %}

Using Relationships In A Query

Finding Records Related To Another One

If you need to find objects related to a model object that you already have, you can take advantage of the generated filterByXXX() methods in the query objects, where XXX is a relation name:

{% highlight php %} <?php $author = AuthorQuery::create()->findPk(1); $books = BookQuery::create() ->filterByAuthor($author) ->orderByTitle() ->find(); {% endhighlight %}

You don't need to specify that the author_id column of the Book object should match the id column of the Author object. Since you already defined the foreign key mapping in your schema, Propel knows enough to figure it out.

Embedding Queries

In SQL queries, relationships often translate to a JOIN statement. Propel abstracts this relational logic in the query objects, by allowing you to embed a related query into another.

In practice, Propel generates one useXXXQuery() method for every relation in the Query objects. So the BookQuery class offers a useAuthorQuery() and a usePublisherQuery() method. These methods return a new Query instance of the related query class, that you can eventually merge into the main query by calling endUse().

To illustrate this, let's see how to write the following SQL query with the Propel Query API:

{% highlight sql %} SELECT book.* FROM book INNER JOIN author ON book.AUTHOR_ID = author.ID WHERE book.ISBN = '0140444173' AND author.FIRST_NAME = 'Leo' ORDER BY book.TITLE ASC LIMIT 10; {% endhighlight %}

That would simply give:

{% highlight php %} <?php $books = BookQuery::create() ->filterByISBN('0140444173') ->useAuthorQuery() // returns a new AuthorQuery instance ->filterByFirstName('Leo') // this is an AuthorQuery method ->endUse() // merges the Authorquery in the main Bookquery and returns the BookQuery ->orderByTitle() ->limit(10) ->find(); {% endhighlight %}

Propel knows the columns to use in the ON clause from the definition of foreign keys in the schema. The ability to use methods of a related Query object allows you to keep your model logic where it belongs.

Of course, you can embed several queries to issue a query of any complexity level:

{% highlight php %} <?php // Find all authors of books published by Viking Press $authors = AuthorQuery::create() ->useBookQuery() ->usePublisherQuery() ->filterByName('Viking Press') ->endUse() ->endUse() ->find(); {% endhighlight %}

You can see how the indentation of the method calls provide a clear explanation of the embedding logic. That's why it is a good practice to format your Propel queries with a single method call per line, and to add indentation every time a useXXXQuery() method is used.

Many-to-Many Relationships

Databases typically use a cross-reference table, or junction table, to materialize the relationship. For instance, if the user and group tables are related by a many-to-many relationship, this happens through the rows of a user_group table. To inform Propel about the many-to-many relationship, set the isCrossRef attribute of the cross reference table to true:

{% highlight xml %}

{% endhighlight %}

Once you rebuild your model, the relationship is seen as a one-to-many relationship from both the User and the Group models. That means that you can deal with adding and reading relationships the same way as you usually do:

{% highlight php %} <?php $user = new User(); $user->setName('John Doe'); $group = new Group(); $group->setName('Anonymous'); // relate $user and $group $user->addGroup($group); // save the $user object, the $group object, and a new instance of the UserGroup class $user->save(); {% endhighlight %}

The same happens for reading related objects ; Both ends see the relationship as a one-to-many relationship:

{% highlight php %} <?php $users = $group->getUsers(); $nbUsers = $group->countUsers(); $groups = $user->getGroups(); $nbGroups = $user->countGroups(); {% endhighlight %}

Just like regular related object getters, these generated methods accept an optional query object, to further filter the results.

To facilitate queries, Propel also adds new methods to the UserQuery and GroupQuery classes:

{% highlight php %} <?php $users = UserQuery::create() ->filterByGroup($group) ->find(); $groups = GroupQuery::create() ->filterByUser($user) ->find(); {% endhighlight %}

One-to-One Relationships

Propel supports the special case of one-to-one relationships. These relationships are defined when the primary key is also a foreign key. For example :

{% highlight xml %}

{% endhighlight %}

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 (BookstoreEmployee::getBookstoreEmployeeAccount(), and BookstoreEmployeeAccount::getBookstoreEmployee()).

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 database level when the foreign keys are created. For databases that do not support foreign keys, this functionality will be emulated by Propel.

{% highlight xml %}

{% endhighlight %}

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

Minimizing Queries

Even if you use a foreign query, Propel will issue new queries when you fetch related objects:

{% highlight php %} <?php $book = BookQuery::create() ->useAuthorQuery() ->filterByFirstName('Leo') ->endUse() ->findOne(); $author = $book->getAuthor(); // Needs another database query {% endhighlight %}

Propel allows you to retrieve the main object together with related objects in a single query. You just use the with() method to specify which objects the main object should be hydrated with.

{% highlight php %} <?php $book = BookQuery::create() ->useAuthorQuery() ->filterByFirstName('Leo') ->endUse() ->with('Author') ->findOne(); $author = $book->getAuthor(); // Same result, with no supplementary query {% endhighlight %}

Since the call to with() adds the columns of the related object to the SELECT part of the query, and uses these columns to populate the related object, that means that a query using with() is slower and consumes more memory. So use it only when you actually need the related objects afterwards.

If you don't want to add a filter on a related object but still need to hydrate it, calling useXXXQuery(), endUse(), and then with() can be a little cumbersome. For this case, Propel provides a proxy method called joinWith(). It expects a string made of the initial query name and the foreign query name. For instance:

{% highlight php %} <?php $book = BookQuery::create() ->joinWith('Book.Author') ->findOne(); $author = $book->getAuthor(); // Same result, with no supplementary query {% endhighlight %}

with() and joinWith() are not limited to immediate relationships. As a matter of fact, just like you can nest use() calls, you can call with() several times to populate a chain of objects:

{% highlight php %} <?php $review = ReviewQuery::create() ->joinWith('Review.Book') ->joinWith('Book.Author') ->joinWith('Book.Publisher') ->findOne(); $book = $review->getBook() // No additional query needed $author = $book->getAuthor(); // No additional query needed $publisher = $book->getPublisher(); // No additional query needed {% endhighlight %}

So with() is very useful to minimize the number of database queries. As soon as you see that the number of queries necessary to perform an action is proportional to the number of results, adding a with() call is the trick to get down to a more reasonnable query count.

with() also works for left joins on one-to-many relationships, but you mustn't use a limit() in the query in this case. This is because Propel has no way to determine the actual number of rows of the main object in such a case.

{% highlight php %} <?php // this works $authors = AuthorQuery::create() ->leftJoinWith('Author.Book') ->find(); // this does not work $authors = AuthorQuery::create() ->leftJoinWith('Author.Book') ->limit(5) ->find(); {% endhighlight %}

However, it is quite easy to achieve hydration of related objects with only one additional query:

{% highlight php %} <?php $authors = AuthorQuery::create() ->limit(5) ->find(); // $authors is a PropelObjectCollection $authors->populateRelation('Book'); foreach ($authors as $author) { // now you can iterate over each author's book without further queries foreach ($author->getBooks() as $book) { // no database query, the author already has a Books collection // do stuff with $book and $author } } {% endhighlight %}

Model-Only Relationships

Propel models can share relationships even though the underlying tables aren't linked by a foreign key. This ability may be of great use when using Propel on top of a legacy database.

For example, a review table designed for a MyISAM database engine is linked to a book table by a simple book_id column:

{% highlight xml %}

{% endhighlight %}

To enable a model-only relationship, add a <foreign-key> tag using the skipSql attribute, as follows:

{% highlight xml %}

{% endhighlight %}

Such a foreign key is not translated into SQL when Propel builds the table creation or table migration code. It can be seen as a "virtual foreign key". However, on the PHP side, the Book model actually has a one-to-many relationship with the Review model. The generated ActiveRecord and ActiveQuery classes take advantage of this relationship to offer smart getters and filters.

Jump to Line
Something went wrong with that request. Please try again.