Custom symfony filters to filter model relations

For an application I was looking for a way to add filters in the generated admin application. We’de like to filter clients based on the articles or orders they have.  Lets take a look at the (simplified) schema:

Account:
columns:
id:             { type: integer, primary: true, autoincrement: true, notnull: true }
accountNumber:  { type: integer, notnull: true }
accountName:    { type: string(255), notnull: true }

Order:
columns:
id:             { type: integer, primary: true, autoincrement: true, notnull: true }
internID:       { type: string(20) }
accountID:      { type: integer, notnull: true }
locationID:    { type: integer }
orderDate:      { type: timestamp }
orderedBy:      { type: integer, notnull: true }
remarks:        { type: string(3000) }
orderDelivered: { type: boolean }
placedOrder:    { type: boolean }
relations:
Account:        { onDelete: CASCADE, local: accountID, foreign: id, foreignAlias: accountOrders }

Article:

columns:

id:            { type: integer, primary: true, autoincrement: true, notnull: true }
accountID:     { type: integer, notnull: true }

articleNumber: { type: string(15) }

description:   { type: string(255) }

currentStock:  { type: integer }

minimumStock:  { type: integer }

packageAmount: { type: integer }

previewFile:   { type: string( 255 ) }

remarks:       { type: string( 3000 ) }

internRemarks: { type: string( 5000 ) }

image:         { type: string( 255 ) }

relations:

Account:       { onDelete: CASCADE, local: accountID, foreign: id, foreignAlias: articleAccount }

In short. We have an account and each account has it own articles. The can order these articles and this is stored in the Order model ( and a bunch of others, which aren’t relevant for this post).

What we want to achieve is that we can filter the client list based on a article number or order id.

To do this I extended the generator.yml to add the two new fields.

config:

fields:
articleNumber:     {label: Artikel nummer, type: string }
orderNumber:     {label: Order nummer, type: string }

Next thing to do is to modify the filter form of the account model. Here we’re going to add the two new fields to the filter form and give them a simple validator.

public function configure()

{

parent::configure();

$this->widgetSchema['articleNumber'] = new sfWidgetFormInputText();

$this->validatorSchema['articleNumber'] = new sfValidatorPass(array('required'=>'false'));

$this->widgetSchema['orderNumber'] = new sfWidgetFormInputText();

$this->validatorSchema['orderNumber'] = new sfValidatorPass(array('required'=>'false'));

}

Now we want to add the filter values to the query so that the list actually gets filtered by the given values. Therefore were going to add a function for each of the filter fields. The function has one purpose and that is to add a where to the doctrine query.

protected function addArticleNumberColumnQuery( Doctrine_Query $query, $field, $values) {

$rootAlias = $query->getRootAlias();

$fieldName = 'articleNumber';

$query->addWhere( sprintf('%s.articleAccount.%s = ?', $rootAlias, $fieldName), $values );

}

protected function addOrderNumberColumnQuery( Doctrine_Query $query, $field, $values) {

$rootAlias = $query->getRootAlias();

$fieldName = 'id';

$query->addWhere( sprintf('%s.accountOrders.%s = ?', $rootAlias, $fieldName), $values );

}

The name of the function is specific and has to match the format add%fieldname%ColumnQuery. The code in the functions are pretty self-explanatory. Somethings are hard coded and could be a  nicer. For example you could name the field accountOrders instead of orderNumber so that you can use the value of $field.

This is the simplest solution I could find for filtering a model based on its relations. If someone knows a better way I’d love to hear about it.