Relationships in Propel

Basic CRUD (Create Update Delete) operations are a bit tedious, but not hard to manage in ORM. Where things get complicated is in the relationships between tables. Here an ORM framework can really save you a lot of work.

In our basic example database (addresses_basic_nofk.sql), we have People and PhoneNumbers in a one-to-many relationship. Every phone number belongs to exactly one person. In code, we'd like our database models to support this, like so:

$p = PeopleQuery::create()->filterByLastName("Bradley")->findOne();

// retrieve children (one-to-many) on demand
foreach ($p->getPhoneNumbers() as $pn) {
  echo "<p>".$pn."</p>";
}

There are a few things we need to do to make that work.

Foreign Key Constraints

The relationship between people and phone numbers in the database is implemented by a person_id column in the PhoneNumbers table that holds the id of the one person each phone number belongs to.

However, just naming the column person_id isn't enough for the automatic generation to pick up on the fact that there is a relationship between people and phone numbers. We need to put a formal foreign key constraint into our database. A FK tells the database that the values in a certain column (person_id) link to the values in another column (people.id). The target column is usually the primary key of another table, as in this case. This allows the database to enforce data consistency, by ensuring that you cannot add a phone number with a bogus person_id (one that doesn't refer to any real person).

To add a foreign key constraint, click the Relation view button at the top of the table structure display. You don't have to name the constraint, it'll automatically generate a name. Just choose the FK column (person_id) and the target column (people.id).

Re-Generate

Any time that you change the database, you need to update your Propel classes. You can do this two ways:

• Blow away all the files Propel created and re-run propel init
• OR, run the generation steps by hand.
  1. Run this command to re-generate the schema.xml file, which will be put in a new generated-reversed-database directory. (The database connection data is already available in the propel.yml file that init created for you).

     vendor\bin\propel reverse

  2. Move that file up a level to the project root, overwriting the old one. (If you're setting up a many-to-many relationship, here is where you edit schema.xml to put isCrossRef="true" on the junction table).
  3. Delete the directory where you put your models and re-generate them. (The name of the directory where it should put the models is in the propel.yml.dist file that init created for you). Also re-dump the autoloader.

     vendor\bin\propel model:build

     composer dump-autoload

Profit!

With the new models, the example now (almost) works. The standard convention for Propel is that table names are singular, so that child collections can be plural. Since I named my tables using plural, the auto-generated model code works like this:

$p = PeopleQuery::create()->filterByLastName("Bradley")->findOne();

// retrieve children (one-to-many) on demand
// note the unfortunate pluralization of "phone_numbers"
foreach ($p->getPhoneNumberss() as $pn) {
  echo "<p>".$pn."</p>";
}

Note that is works the other way too, but each phone number has only one person.

// just grab the first phone number
$pn = PhoneNumbersQuery::create()->findOne();

// retrieve single child (still unfortuate naming)
echo "<p>".$pn->getPeople()."</p>";

The Propel docs give many more examples, including how to add, update and delete object and their related objects.