symfony admin filter with join

Question

I have a table, heading, that has an import_profile_id. import_profile has a bank_id.

On my headings list page in my admin, I'd like to add the ability to filter by bank_id. However, since heading doesn't have a bank_id - it needs to go through import_profile to get that - I can't just add a bank_id field and expect it to work.

Can anyone explain how to do this? The closest thing I've found is this post but I don't think it really addresses my issue.

Answer 1

This can be done by using virtual columns like the post you found. The virtual column is a way to add a new criteria to filter using the autogenerated filter provided by symfony. It works like this:

1 - Go to the generator.yml of the admin module and add the name of the virtual column that will create and add

<!-- apps/backend/modules/module_name/config/generator.yml -->
filter:
     [virtual_column_name, and, other, filter, columns]

2 - In your lib/filter/{TableName}FormFilter.class.php (I think in your case must be HeadingFormFilter) you have to define that virtual column in the configure() method

  public function configure()
  {
      //Type of widget (could be sfWidgetFormChoice with bank names)
      $this->widgetSchema['virtual_column_name'] =  new sfWidgetFormInputText(array(
          'label' => 'Virtual Column Label'
      ));

      //Type of validator for filter
      $this->validatorSchema['virtual_column_name'] = new sfValidatorPass(array ('required' => false));
  }

3 - Override the getFields() of that class to define it in the filter and set the filter function

public function getFields()
{
  $fields = parent::getFields();
  //the right 'virtual_column_name' is the method to filter
  $fields['virtual_column_name'] = 'virtual_column_name';
  return $fields;
}

4 - Finally you have to define the filter method. This method must be named after the add...ColumnQuery pattern, in our case must be addVirtualColumnNameColumnQuery(not a happy name choice :P), so

public function addVirtualColumnNameColumnQuery($query, $field, $value)
{
     //add your filter query!
     //for example in your case
     $rootAlias = $query->getRootAlias();
     $query->innerJoin($rootAlias . '.ImportProfile ip')
           ->andWhere('ip.BankId = ?', $value);

     //remember to return the $query!
     return $query;
}

Done! You can know filter by bank_id.