Dev 1.x

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.

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:

<?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();

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():

<?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();

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.

<?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!

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

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

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

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

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

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.

TipPropel 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:

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

Using Relationships In A Query

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:

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

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:

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;

That would simply give:

<?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();

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:

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

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:

<table name="user">
  <column name="id" type="integer" primaryKey="true" autoIncrement="true"/>
  <column name="name" type="varchar" size="32"/>
</table>

<table name="group" identifierQuoting="true">
  <column name="id" type="integer" primaryKey="true" autoIncrement="true"/>
  <column name="name" type="varchar" size="32"/>
</table>

<table name="user_group" isCrossRef="true">
  <column name="user_id" type="integer" primaryKey="true"/>
  <column name="group_id" type="integer" primaryKey="true"/>
  <foreign-key foreignTable="user">
    <reference local="user_id" foreign="id"/>
  </foreign-key>
  <foreign-key foreignTable="group">
    <reference local="group_id" foreign="id"/>
  </foreign-key>
</table>

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:

<?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();

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

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

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:

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

More than two primary keys

If you define a cross-table with a primary key based on more than two columns, Propel will generate different method signatures for all generated relation methods. It generates for each additional primary key column that doesn’t belong to the first two outgoing relations a new method parameter with its type and its name.

Example:

<table name="user_group" isCrossRef="true">
  <column name="user_id" type="integer" primaryKey="true"/>
  <column name="group_id" type="integer" primaryKey="true"/>
  <column name="type" type="integer" primaryKey="true"/>
  <column name="additional_value" type="varchar"/>
  <foreign-key foreignTable="user">
    <reference local="user_id" foreign="id"/>
  </foreign-key>
  <foreign-key foreignTable="group">
    <reference local="group_id" foreign="id"/>
  </foreign-key>
</table>

You’ll get these methods:

<?php
$user->addGroup(User $group, int $type):$this;
$user->removeGroup(User $group, int $type):$this;
$user->countGroups($position = null, Criteria $criteria = null, ConnectionInterface $con = null):int;
$user->getGroups($position = null, Criteria $criteria = null, ConnectionInterface $con = null):Group[]|ObjectCollection;
$user->createGroupsQuery($position = null, Criteria $criteria = null):GroupsQuery;

$user->getGroupTypes($criteria = null):ObjectCombinationCollection;
$user->setGroupTypes(array $groupTypes):$this;

# Query class will be extended as well
UserQuery::create()->filterByGroup(Group $group, $comparison = Criteria::EQUAL)->find();

same with the opposite Group object.

Note here that you not only have different method signatures but also some additional methods.

getGroupTypes(int $type) returns a ObjectCombinationCollection where in this example the first place of this combination is the Group object and second place is the integer $type. The order is defined in your schema.

<?php
$groupTypes = $user->getGroupTypes(); //[ [Group, int], [Group, int], ...]
foreach ($groupTypes as list($group, $type)) { # this syntax is only available in PHP >= 5.5
    echo "{$user->getName()} is in group {$group->getName()} as $type";
}

To find for example all groups from $hans where the relation is from type 2 you can getGroups:

<?php
$hans = new User();
$group = new Group();
$groups = $hans->getGroups(2); //hits always the database, so doesn't access its internal state.

$hans->addGroup($group, 3);
$groups->getGroups(3); //returns empty array since you need to save first
$groups->getGroupTypes(); //returns array with one entry: [ [$group, 3] ], since this returns its internal state.

$hans->save();
$groups->getGroups(3); //returns now a list with one group: [$group]

$hans->removeGroup($group, 3);
$groups->getGroups(3); //returns still a list with one group: [$group], because you need to call save first
$groups->getGroupTypes(); //returns empty list
$hans->save();

$groups->getGroups(3); //return empty list

If you want to filter for example by type > 2 then you need to use getGroupTypes:

<?php
$filter = UserGroupQuery::create()->filterByType(2, Criteria::GREATER_THAN);

$groupTypes = $hans->getGroupTypes($filter);
$groups = $groupTypes->getObjectsFromPosition(1); //first position is the Group object, second is the `type`

//or
$groups = $hans->getGroups($position = null, $filter);

//or
$groups = $hans->createGroupQuery($position = null, $filter)->find();

//createGroupQuery allows your to set a formatter and additional conditions
$groups = $hans->createGroupQuery($position = null, $filter)
    ->joinWith('OtherRelation')
    ->setFormatter(ModelCriteria::FORMAT_ON_DEMAND)
    ->find(); //groups which relation's type is greater than 2, with addition join and onDemand formatter

//or
$groups = $hans->createGroupQuery()
    ->joinWith('OtherRelation')
    ->setFormatter(ModelCriteria::FORMAT_ON_DEMAND)
    ->find(); //all groups, with addition join and onDemand formatter

More examples of using those methods:

<?php
$groups = $groupTypes->getObjectsFromPosition(1); //[Group, Group, ...]
$types = $groupTypes->getObjectsFromPosition(2);  //[int, int, ...]

$newGroupTypes = array();
$newGroupTypes[] = array(new Group(), 2);
$newGroupTypes[] = array(new Group(), 3);
$user->setGroupTypes($newGroupTypes);

//same as
$user->addGroup(new Group(), 2);
$user->addGroup(new Group(), 3);

$user->save();

Since this is as usual automatic generated, you can just use IDE’s code completion to see how to call those methods.

More than two foreign keys

Note that the order of <foreign-key> elements are pretty important. If you have three outgoing foreign keys that where all are primary keys, then only the first two foreign keys will be used for cross relation methods. Additional non-fk primary keys will be attached as filter to those methods.

Example:

<table name="user_group" isCrossRef="true">
  <column name="user_id" type="integer" primaryKey="true"/>
  <column name="group_id" type="integer" primaryKey="true"/>
  <column name="type_id" type="integer" primaryKey="true"/>
  <foreign-key foreignTable="user">
    <reference local="user_id" foreign="id"/>
  </foreign-key>
  <foreign-key foreignTable="group">
    <reference local="group_id" foreign="id"/>
  </foreign-key>
  <foreign-key foreignTable="types">
    <reference local="type_id" foreign="id"/>
  </foreign-key>
</table>

In this example we have three foreign keys whereas each of them is part of the primary key. This generates methods like:

<?php
$user->addGroup(Group $group, Type $type):$this;
$user->removeGroup(Group $group, Type $type):$this;
$user->countGroups(Type $type = null):int;
$user->getGroups(Type $type = null, ...):Group[];

$user->getGroupTypes($criteria = null):ObjectCombinationCollection;

Whereas a schema likes this:

<table name="user_group" isCrossRef="true">
  <column name="user_id" type="integer" primaryKey="true"/>
  <column name="group_id" type="integer" primaryKey="true"/>
  <column name="type_id" type="integer" primaryKey="true"/>
  <foreign-key foreignTable="user">
    <reference local="user_id" foreign="id"/>
  </foreign-key>
  <foreign-key foreignTable="types">
    <reference local="type_id" foreign="id"/>
  </foreign-key>
  <foreign-key foreignTable="group">
    <reference local="group_id" foreign="id"/>
  </foreign-key>
</table>

Generates methods like:

<?php
$user->addType(Type $type, Group $group);
$user->removeType(Type $type, Group $group);
$user->countTypes(Group $group = null, ...);
$user->getTypes(Group $group = null, ...);

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:

<table name="bookstore_employee" description="Employees of a bookstore">
  <column name="id" type="integer" primaryKey="true" autoIncrement="true"/>
  <column name="name" type="varchar" size="32"/>
</table>

<table name="bookstore_employee_account" description="Bookstore employees' login credentials">
  <column name="employee_id" type="integer" primaryKey="true"/>
  <column name="login" type="varchar" size="32"/>
  <column name="password" type="varchar" size="100"/>
  <foreign-key foreignTable="bookstore_employee">
    <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 (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.

<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="id"/>
  </foreign-key>
</table>

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:

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

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.

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

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:

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

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:

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

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 reasonable query count.

Tipwith() 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.

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

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

<?php
$authors = AuthorQuery::create()
  ->limit(5)
  ->find();
// $authors is a Propel\Runtime\Collection\ObjectCollection
$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
  }
}

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:

<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"/>
</table>

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

<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"/>
  <!-- Model-only relationship -->
  <foreign-key foreignTable="book" onDelete="CASCADE" skipSql="true">
    <reference local="book_id" foreign="id"/>
  </foreign-key>
</table>

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.

Next: Transactions →

↟ Jump To Top

Setup

Basics

Reference

Behaviors

Cookbook

Basic Relationships

Save Cascade

Using Relationships In A Query

Embedding Queries

Many-to-Many Relationships

More than two primary keys

More than two foreign keys

One-to-One Relationships

On-Update and On-Delete Triggers

Minimizing Queries

Model-Only Relationships

Setup

Basics

Reference

Behaviors

Cookbook

Basic Relationships

Inserting A Related Row

Save Cascade

Reading Related Object Properties

Using Relationships In A Query

Finding Records Related To Another One

Embedding Queries

Many-to-Many Relationships

More than two primary keys

More than two foreign keys

One-to-One Relationships

On-Update and On-Delete Triggers

Minimizing Queries

Model-Only Relationships