Symfony: sfWidgetFormDoctrineChoice: how to retrieve a specific field

Question

Here's my problem, I checked the API and a bunch of other sites talking about that but I couldn't find any solution.

Some background info:

Tables:

Resources(submit_user,...);
Users(id, username,...); primary key(id);

In 'Resources': foreign key(submit_user) references Users(id);

Symfony form:

ResourcesForm

Widget and validator for submit_user:

'submit_user' => new sfWidgetFormDoctrineChoice(array('model' => 'Users', 'add_empty' => false)) //Alternatively, 'model' =>getRelatedModelName('Users')

'submit_user' => new sfValidatorDoctrineChoice(array('model' => 'Users', 'required' => false))

Obviously, as I want to submit the form as it is, it doesn't work since the foreign key constraint isn't satisfied. (I need the username field, not the id for the submit_user field).

Is there any way I can retrieve the username field instead of the id field? I really can't modify the database.

Thanks!

Edit: here's what's inside the schema.yml for the two tables.

Resources:
  connection: doctrine
  tableName: resources
  columns:
    id:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: true
      autoincrement: false
    aggregation:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    version:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    version_status:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    interactivity_type:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    interactivity_level:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    semantic_density:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    difficulty_level:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    learning_time:
      type: integer(4)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    url:
      type: string(100)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    submit_user:
      type: string(15)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    submit_date:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      default: '0000-00-00 00:00:00'
      notnull: true
      autoincrement: false
    last_updated:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    active:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    type:
      type: integer(1)
      fixed: false
      unsigned: true
      primary: false
      default: '1'
      notnull: false
      autoincrement: false
    catalogue_entry:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    author:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    publisher_producer:
      type: string()
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    funding_body:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    size:
      type: integer(8)
      fixed: false
      unsigned: true
      primary: false
      notnull: false
      autoincrement: false
    duration:
      type: time(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    cost:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    copyright:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    filename:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    protect:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    title:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    stat_hits:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    keywords:
      type: string(250)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    password:
      type: string(45)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    ResourceContext:
      local: id
      foreign: id
      type: many
    ResourceDescriptions:
      local: id
      foreign: id
      type: many
    ResourceEndUser:
      local: id
      foreign: id
      type: many
    ResourceLanguages:
      local: id
      foreign: id
      type: many
    ResourceMetadata:
      local: id
      foreign: id
      type: many
    ResourceRelations:
      local: id
      foreign: target
      type: many
    ResourceRelations_2:
      class: ResourceRelations
      local: id
      foreign: source
      type: many
    Users:
      local: submit_user
      foreign: username
      type: one
Users:
  connection: doctrine
  tableName: users
  columns:
    id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    username:
      type: string(15)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    role:
      type: string(12)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    firstname:
      type: string(20)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    middlename:
      type: string(20)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    lastname:
      type: string(30)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    email:
      type: string(100)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    created:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
  relations:
    Resources:
      local: username
      foreign: submit_user
      type: many

And here's now the method used in the 'table_method' option for the submit_user widget (seems to work perfectly fine but how can I use this method for the validation?):

public function getUsernames(){

    $query = Doctrine_Query::create()
            ->select('u.username')
            ->from('Users u')
            ;

    return $query->execute();
}

...And how I'm using that now in ResourcesForm.class.php:

$choice = new sfWidgetFormDoctrineChoice(array('model' => 'Users', 'table_method' => 'getUsernames', 'order_by' => array('name', 'asc')));
$choices = $choice->getChoices();
$this->widgetSchema['submit_user'] = new sfWidgetFormChoice(array('choices' => $choices));

$this->validatorSchema['submit_user'] = new sfValidatorChoice(array('choices' => $choices));

Answer 1

The sfWidgetFormDoctrineChoice widget allows you to pass an optional parameter called table_method, which allows you to call a custom method in the User model to return the values you specify.

-- Update 3.16.12 --

I had to add some custom code to one of my Object Forms due to the fact that Doctrine would issue multiple queries for the same data, as such it seems that the following change would work well in your case:

$choice = new sfWidgetFormDoctrineChoice(array('model' => $model, 'table_method' => 'getUsernames', 'order_by' => array('name', 'asc')));

// Loads the data once
$choices = $choice->getChoices();

// Lets set the keys the same as the value so when form is bound
//  it should pass valid test AND be able to insert/update accordindly
$choices = array_combine($choices, $choices);

// Notice the switch from sf*FormDoctrineChoice to the default sf*FromChoice API calls
// This is because we already have the data loaded, so no need to call on doctrine's
// versions again

$this->widgetSchema['submit_user'] = new sfWidgetFormChoice(array('choices' => $choices));

// Normally this would have an array_keys() call, but aren't concerned with ids
// we are concerned with user names, so it should be a straight pass through
$this->validatorSchema['submit_user'] = new sfValidatorChoice(array('choices' => $choices));