Using Multiple Primary Keys in Admin Generator


Sometimes, the table we’re using force us to use multiple primary keys as shown in the schema below (config/schema.yml):

connection:            propel
defaultIdMethod:       native
package:               lib.model

classes:
  State:
    tableName:         state
    columns:
      id:              { type: varchar, size: 2, primaryKey: true }
      country_id:      { type: varchar, size: 2, primaryKey: true }
      name:            { type: varchar, size: 50, index: true }
      created_at:

By default, the symfony Admin Generator will only support single primary key. Since symfony 1.2, which adds sfRoute class, using multiple primary keys is possible by a configuration modifications and adds a few lines of code.

So now, generate the admin module by issuing:

symfony propel:generate-admin frontend State

Look at the generated route in apps/frontend/config/routing.yml:

state:
  class: sfPropelRouteCollection
  options:
    model:                State
    module:               state
    prefix_path:          state
    column:               id
    with_wildcard_routes: true

Only a single primary key was generated, so the idea is concatenate the primary keys as a single column, separated by a dash (-) for example.

Modify the route:

state:
  class: sfPropelRouteCollection
  options:
    model:                State
    module:               state
    prefix_path:          state
    column:               countryandstate
    with_wildcard_routes: true
    model_methods:
      object:             doSelectForRoute
  requirements:           { countryandstate: '[a-zA-Z0-9\.\_\-\:]+' }

As seen in the modified route, we change the column name to countryandstate, add a model-methods for object configuration and change the countryandstate column requirements.

When an object converted as a route, the sfRoute class will check for toParams() method, so we can provide the value of countryandstate column by adding this code:

// lib/model/State.php
<?php

class State extends BaseState
{
  public function toParams()
  {
    return array('countryandstate' => implode('-', array($this->getCountryId(), $this->getId())));
  }
}

A model-methods for object configuration in the routing above, changes the method used by the sfRoute class to retrieve an object which match a route.

// lib/model/StatePeer.php
<?php

class StatePeer extends BaseStatePeer
{
  /**
   * Retrieve State object for routing.
   *
   * @param array $parameters  The route parameters
   * @return State
   */
  public static function doSelectForRoute($parameters)
  {
    if (!isset($parameters['countryandstate']))
    {
      return null;
    }

    $c = new Criteria();
    list($country, $state) = explode('-', $parameters['countryandstate']);
    $c->add(StatePeer::COUNTRY_ID, $country)
      ->add(StatePeer::ID, $state);

    return StatePeer::doSelectOne($c);
  }
}

The rest is, to customize the generated module and adjusting the form as you need.

About these ads

, , ,

  1. #1 by Thomas on April 22, 2009 - 8:10 pm

    In the function doSelectForRoute, consider what happens if $parameters[‘countryandstate’] is not set. The criteria object, $c, is not modified which means the call to StatePeer::doSelectOne will return the first object found after a SELECT with empty criteria. If the table is not empty, this will simply be the first record in the table which is clearly not the intended result.

    I suggest reversing the logic of the if statement. That is, “if (!isset(…)) { return array();}”. Make this the first line of the function and the rest of the code can be linear (no “if”).

    • #2 by tohenk on April 25, 2009 - 11:56 am

      Thanks for your feedback.

  2. #3 by zevero on June 10, 2009 - 7:07 pm

    You saved my day!!!

    Thanks!

  3. #4 by waouf on September 15, 2009 - 5:12 am

    Does it also work for Doctrine?

    • #5 by tohenk on September 16, 2009 - 11:21 am

      I’m not familiar with Doctrine, but the method may still the same.

      You can modify the route as the above example, add a toParams() method to your Doctrine Model and a doSelectForRoute() method (or you can name it whatsever) to your Doctrine Table.

  4. #6 by patdec on September 19, 2009 - 5:28 pm

    It doesn’t work for me. The toParams method is called when I want add a new row ( action “New” ).

    The issue is one of both keys is not known at this moment. The form is not filled yet.

    My schema.yml:
    LigneFeuilleHeures:
    columns:
    feuille_heures_id: { type: integer, primary: true }
    offre_id: { type: integer, primary: true }
    relations:
    FeuilleHeures: { local: feuille_heures_id, foreign: id, foreignAlias: LigneFeuilleHeures }
    Offre: { local: offre_id, foreign: id, foreignAlias:LigneFeuilleHeures }

    “offre_id” will be selected in form. But this key is required in toParams:

    public function toParams() {
    echo “Param:”.$this->getOffreId().”-“.$this->getFeuilleHeuresId();
    return array(‘offrefeuille’ => implode(‘-‘, array($this->getOffreId(), $this->getFeuilleHeuresId())));
    }

    Why toParams() is called when executeNew() is called ?

    • #7 by tohenk on September 22, 2009 - 9:36 am

      Does it throw an Exception? Have you try clearing the cache? toParams() method is called when an object is converted to a route.

  5. #8 by hwiens on December 8, 2009 - 3:34 pm

    hi,
    unfortunately this trick doesn’t work for symfony 1.4. The _list_td_batch_actions.php triggers a fatal error. It tries to echo the primaryKey and while the primaryKey is an array, a simple ‘echo’ doesn’t work.

    Anyone here with a solution for symfon 1.4 ?

    hwiens

  6. #9 by Tomor on January 2, 2010 - 7:11 pm

    My response to this

    http://groups.google.com/group/symfony-users/browse_thread/thread/9c783fc59dc281ae/

    topic:

    I’ve been dealing with this problem for two days now. I’ve found two solutions (working with symfony 1.4)
    1) create a getter for composite primary key and use this ( return $keypart1.’-‘.$keypart2.’-‘.$keypart3) and use this in the url…
    2) today I’ve found another solution, I’ think it’s better…

    For you try to change :Array (in the routing settings) to the url part compound from collumns of your composite primary key …

    int_presence: 
       class: sfDoctrineRouteCollection 
       options: 
         model:                InternationalPresence 
         module:               int_presence 
         prefix_path:          int_presence 
         column:               competitor_id/:iso_country_code/:presence_type_id
         with_wildcard_routes: true
    

    The result is longer url, but it’s ok for me… and it works for me.

    What doesn’t work for my is deleting rows on the list page … using the checkboxes.

    And I’ve had another problem – one column from my composite key is integer autoincrement (second is string). This autoincrementation didn’t work, and this was cousing an arror during creation a new row (symfony wan’t to redirect to a edit page, but in the object there wasn’t generated id yet…) So I’ve created my method for generating the key and set it manually in my form object in the doUpdateObject() method :

    class PageForm extends BasePageForm
    {
      protected function doUpdateObject($values)
      {
        // Set id if it's not set
        if (empty($values['id']))
        {
          $values['id'] = Doctrine::getTable('Page')->getNextId();
        }
    	
        parent::doUpdateObject($values);
      }
    }
    

    getNextId is in the table class…

    class PageTable extends Doctrine_Table
    {
    	public function getNextId()
    	{
    		return $this->getMaxId() + 1;
    	}
    	
    	public function getMaxId()
    	{
    		$q = $this->createQuery('p')
    			->select('MAX(p.pgid) as maxid');
    		
    		return $q->fetchOne()->maxid;
    	}
    }
    

    Hope this will help somebody …

  7. #10 by Amambay on May 17, 2010 - 8:39 am

    I have several problems with this:
    first: I have a module generated from a relationship (url_category), whose primary key is (URL_ID-category_id). when I try to edit a list item, I get the following error: 404 | Not Found | sfError404Exception Action “suggestions/56″ does not exist.

    Second: When I try to use the action to eliminate batch I get the following error: 500 | Internal Server Error | PropelException
    [Wrapped: SQLSTATE [22P02]: Invalid text representation: 7 ERROR: input syntax is not valid for integer: “Array”]

    If someone could help me urgently to resolve these problems, I would greatly appreciate them.

  8. #11 by Tomor on May 18, 2010 - 1:38 pm

  9. #12 by John E. on June 1, 2010 - 7:27 pm

    Thank you Tomor, your solution saved my day!

    I really couldn’t figure out what the issue “Action “moduleName/rowName” does not exist” was all about, but adding an array of primary keys to routing.yml solved the problem.

    Like this:
    column: competitor_id/:iso_country_code/:presence_type_id

  10. #13 by Enrique on February 1, 2013 - 12:23 am

    Thks Tomor, I’ve been dealing with the same problem for a long time, but your solution work fine.
    Like this:
    column: competitor_id/:iso_country_code/:presence_type_id

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: