How to fill a Symfony choice widget dynamically with the result of a DB query

Question

In one of my forms I would like to have a dropdown (sfWidgetFormChoice) where the options are generated dynamically by executing a query on the database.

To be a little bit more precise, I'm going to list all versions which I have in the table. The query looks something like this:

select distinct version from mytable order by version desc

What I have so far but doesn't work:

class myForm extends sfForm

$query = "select distinct version from mytable order by version desc";

$versions = Doctrine_Manager::getInstance()->getCurrentConnection()->fetchAssoc($query);

public function configure()

$this->setWidgets(array('version' => new sfWidgetFormChoice(array('choices' => self::$versions))));

Edit:

Thansk guys for your answers! Much appreciated!

Anyhow your solutions base on having a model of the table. I'd rather have a PDO directly as it's faster.

In the Symfony Documentation I found what I was looking for under "Using Raw SQL Queries".

So I extended my Form to end up with this:

class myForm extends sfForm
{
  public function getVersions()
  {
    $connection = Doctrine_Manager::connection();
    $query      = "select distinct version from mytable order by version desc";
    $statement  = $connection->prepare($query);
    $statement->execute();
    $resultset = $statement->fetchAll(PDO::FETCH_COLUMN, 0);

    return $resultset;
  }

  public function configure()
  {
    $this->setWidgets(array('version' => new sfWidgetFormChoice(array('choices' => self::getVersions()))));
  }
}

As a result of this my dropdown gets properly filled with what is in my table, yay! BUT I get also warnings:

Warning: Invalid argument supplied for foreach() in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/database/sfDoctrineConnectionProfiler.class.php on line 196

Warning: join() [function.join]: Invalid arguments passed in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/database/sfDoctrineConnectionProfiler.class.php on line 141

Warning: Invalid argument supplied for foreach() in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/database/sfDoctrineConnectionProfiler.class.php on line 196

Warning: Invalid argument supplied for foreach() in lib/vendor/symfony/lib/plugins/sfDoctrinePlugin/lib/debug/sfDoctrineConnectionProfiler.class.php on line 117

Any idea what I'm doing wrong here??? Oddly enough the dropdown looks fine.

Answer 1

Use the table_method option of sfWidgetFormDoctrineChoice like this :

In your form class do this :

$this->setWidgets(array('version' => new sfWidgetDoctrineChoice(array('model' => 'Version', 'table_method' => 'getData')));

then in the versionTable.class.php file create a getData() (you can call this anything) function that returns a collection of objects :

public function getData() {
    $this->getInstance()->createQuery()
          ->orderBy('version desc')
          ->execute();
}

Answer 2

You can use sfWidgetFormDoctrineChoice

public function configure(){

$this->widgetSchema['version'] = new sfWidgetFormDoctrineChoice(array('model' => 'YourModel', 'query' => Doctrine::getTable('YurModel')->getYourDataQuery(), 'add_empty' => true));

}

And in your modelTable.class.php

 public function getYourDataQuery()
    {
          //Your query

            $q = $this->createQuery('a');         

        return $q;
     }

Answer 3

I finally found the solution I was looking for by myself. This accesses the DB now directly. The reason why I don't use FETCH_COLUMN anymore is that this would create an array where the key is an Id starting from 0 and the value is whatever is returned by the query itself. Instead I wanted to have the key and value to be whatever the query delivers. This is why the query now delivers two columns being the same and then FETCH_KEY_PAIR does the rest.

In case anyone else is interested in how I solved this, here is my code which is filling the drop down properly and causing no errors.

class myForm extends sfForm
{
  public function getVersions()
  {
    $connection = Doctrine_Manager::getInstance()->getCurrentConnection()->getDBh();
    $query      = "select distinct version as key, version as value from mytable order by version desc";
    $statement  = $connection->prepare($query);
    $statement->execute();
    $resultset = $statement->fetchAll(PDO::FETCH_KEY_PAIR);

    return $resultset;
  }

  public function configure()
  {
    $this->setWidgets(array('version' => new sfWidgetFormChoice(array('choices' => self::getVersions()))));
  }
}

Answer 4

Thank you sir, I really appreciate your solution. you have saved my day!

Just in case anyone who's having similar issues. I ran into a PDO not found problem when using the solution. (FatalErrorException: Error: Class '.......\Controller\PDO' not found in ) Reason being: https://groups.google.com/forum/#!topic/symfony2/mR22XXKPQrk "You are misusing the PHP namespaces. When you are in a namespaced code, using PDO means a class name relative to the current namespace. You have 2 solutions to fix your issue: using a fully qualified class name like \PDO or adding a use statement to import the class in the current namespace."

So After adding the '\' in front of PDO::, the solution works perfectly.

Thanks!