Adding many-to-many tables to an existing symfony project
This is a symfony 1.0 project, but I think everything applies equally to 1.1, except the command syntax has changed from propel-load-data
to propel:load-data
In this case, the application helps nurses collect and analyze data about how they’re administering intravenous therapies to their patients. At first they wanted plain text lists of the IV drugs and fluids, but in order to filter based on them, we needed to change them to discreet fields. In the View, it means changing a couple of fields from plain text to arrays of checkboxes. In the Model, it means adding separate tables, and a “through class” associating the therapy with the new class.
Changing the database schema and the generated object model
The original design had only two classes/tables: Patients
, each having one or more Therapies
. Now, each Therapy
can have 0 or more Drugs
and Fluids
.
First I added additional tables to my schema.yml, using the column naming conventions so symfony will get the table relationships right:
drug: id: name: varchar(255) created_at: updated_at: fluid: id: name: varchar(255) created_at: updated_at: therapy_drug: id: therapy_id: drug_id: therapy_fluid: id: therapy_id: fluid_id: |
Propel will generate the table creation SQL:
./symfony propel-build-sql |
But I don’t want to obliterate my existing data, so I cut the 4 CREATE TABLE
statements for my 4 new tables out of data/sql/lib.model.schema.sql and put it in a file named data/sql/create_drugs_fluids.sql (and added it to Subversion with svn add
)
Now I can feed that file to MySQL
mysql -uroot iv_log < data/sql/create_drugs_fluids.sql |
Next, I wrote a fixture file in YAML.
Then I have to clear the cache and rebuild the model in order to populate my new tables from the fixture, but I have all the objects ready to go!
./symfony cc ./symfony propel-build-model ./symfony propel-load-data frontend |
Nicer accessors in the Model
Next I’ll do some work in the model layer to make things more convenient and logical for accessing the Drug
and Fluid
objects attached to each Therapy
.
Add accessors to the Therapy model that return the array of through-class objects (TherapyDrug and TherapyFluid), for example in lib/model/Therapy.php:
public function getDrugs($criteria = null, $con = null) { return $this->getTherapyDrugsJoinDrug($criteria, $con); } |
(and a similar getFluids
method)
Make a list of through-class objects display like a list of the associated thing. In lib/model/TherapyDrug.php:
public function __toString () { $s = $this->getDrug(); return $s->__toString(); # i've had problems not doing this explicitly } |
(and the same for TherapyFluid.php)
Now make sure the attached objects have a good __toString()
method. In lib/model/Drug.php
/** * Method __toString * * @return string the drug name * @package lib.model */ public function __toString () { return $this->getName(); } |
Changing the Admin Generator GUI
Now my database and model are all set, I need to make the new tables/objects available in the GUI. I am using the Admin Generator for all the parts of the application.
First, I want them to be able to edit the list of drugs and fluids, so I build admin modules:
./symfony propel-init-admin frontend drug Drug ./symfony propel-init-admin frontend fluid Fluid |
But I don’t want users to delete these values and leave orphaned rows in the related tables, or delete all related Therapy objects, so I’ll remove the ‘delete’ ability in each new module’s config/generator.yml, for example, in apps/frontend/modules/drug/config/generator.yml,
generator: class: sfPropelAdminGenerator param: model_class: Drug theme: default list: display: - =name object_actions: _edit: ~ edit: display: - name actions: _save: params: confirm=This will affect all existing log records that use this drug, OK? _list: - |
OK, now to add it to the Therapy object’s admin module. I’m going to need it in three places:
- The list view
- The edit form
- The filter list on the list view
List view
In the list view, I unfortunately can’t just tell it to display the “drug” or “therapy_drug” column, because the Admin generator will print the results as “Array”. So I make a partial to display the list of related objects. I’m going to use the same partial for list, edit and filter views using the $type variable the admin generator provides to tell them apart.
I define my partial in apps/frontend/modules/therapy/templates/_drugs.php
<!-- begin _drugs partial for type '<?php echo $type ?>' --> <?php switch ($type): ?> <?php case 'list': echo implode(', ', $therapy->getDrugs()); ?> <?php break; case 'edit': // this is handled by the admin generator ?> <?php break; case 'filter': // coming in a minute ?> <?php break; endswitch; ?> <!-- end _drugs partial --> |
Now I just have to add the partials to the list of fields that display in list view, in
list: fields: # ... - _drugs - _fluids |
And hooray! My list of Therapy
objects now displays their associated Drug
and Fluid
objects:
Edit view
The edit view doesn’t really need the partial at this point, we can get a list of checkboxes in the edit form just by hacking the correct stuff into apps/frontend/modules/therapy/config/generator.yml:
edit: display: # ... - drug - fluid fields: # ... drug: name: Drug(s) type: admin_check_list params: through_class=TherapyDrug fluid: name: Fluid(s) type: admin_check_list params: through_class=TherapyFluid |
I had to make a little tweak to the .sf_admin_checklist li
style in web/css/main.css, but now I have a nice set of working checkboxen!
Filter view
This will take two stages. First I put this sort of thing in my _drugs.php and _fluids.php partials:
<?php break; case 'filter': $drugs = DrugPeer::doSelect(new Criteria); // get the list of all the possible drugs echo select_tag('filters[drug]', objects_for_select( $drugs, 'getId', 'getName', isset($filters['drug']) ? $filters['drug'] : '', 'include_blank=true') ); ?> |
and tell the Admin Generator to use the partials, back in generator.yml:
list: # ... filters: # ... - _drugs - _fluids |
Now I have a nice widget in my filters box, but it doesn’t do anything quite yet:
To get it working I’ll need to help out the filter action, by extending the addFiltersCriteria action generated by the admin generator. I will edit my copy in apps/frontend/modules/therapy/actions/actions.class.php:
protected function addFiltersCriteria($c) { if (isset($this->filters['drug']) && $this->filters['drug'] != '') { $c->addJoin( TherapyPeer::ID, TherapyDrugPeer::THERAPY_ID, Criteria::LEFT_JOIN); $c->add(TherapyDrugPeer::DRUG_ID, $this->filters['drug']); } if (isset($this->filters['fluid']) && $this->filters['fluid'] != '') { $c->addJoin( TherapyPeer::ID, TherapyFluidPeer::THERAPY_ID, Criteria::LEFT_JOIN); $c->add(TherapyFluidPeer::FLUID_ID, $this->filters['fluid']); } return parent::addFiltersCriteria($c); } |
Now the action will add additional constraining filter criteria before displaying the list of Therapy objects, if the filters have been set. That will let the user search for only records with a particular combination of values, for example.
Whew, that got a little bit involved but I’m sure it will be easier next time.
The big breakthrough for me learning how to work with the admin generator was when I realized that it generates all its files in the cache directory, so you can look in the cache at how it’s doing everything, and override as necessary with partials and actions when you run past what you can do with generator.yml.
[…] Adding many-to-many tables to an existing symfony project […]
Nice article, I suggest you take a look at this plugin for future work where you need to migrate the schema on an existing live database:
http://www.symfony-project.org/plugins/sfPropelSqlDiffPlugin
Works really well for me so far.
-Josh
That plugin looks great. Thanks, Josh!
Hey Nathan,
We are looking for someone to do some web design, are you interested?
Gary Pedretty Frontier Alaska
gary@frontierflying.com
Nice tut – the filters become especially important if you are dealing with many records/objects.
how to use composite FKs as composite PK ? i tried in my schema, it works only inside mySql Query tool, but it cant run from pages, thus i cant add new field from webpages..
i didnt use ( id: ~ ) in that table..
Sara, I’ve run in to trouble with composite keys using both Doctrine and Propel, so I always add a unique
id
field to tables I’m going to use with Symfony, even though it’s incorrect from a pure RDBMS perspective.That’s why I have
id
columns in thetherapy_drug
andtherapy_fluid
examples above.