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:

1. The list view
2. The edit form
3. 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.