Sort on foreign key and partials on admin generator

I have created a correct working version to sort on foreign keys.

In the generator.yml file you can now define on which (foreign)field(s) you want to sort.

For example you use the sfGuard plugin and add a relation to a profile table which contain firstname, nameadditions, a lastname and an emailaddress. In your user-list you want to see the username, fullname and emailaddress. Then your generator.yml file should contain the following lines to make the sorting on foreign fields to work (my profile class is simply called user and the primary key id-column is called userid):

generator:

class: sfPropelAdminGenerator param:

model_class: sfGuardUser theme: default

list:

title: Users display: [ =username, _show_fullname, _show_emailaddress] fields:

show_fullname:

name: Fullname join_fields: [sfGuardUserPeer::ID, UserPeer::USERID] sort_column: [UserPeer::FIRSTNAME, UserPeer::NAMEADDITIONS, UserPeer::LASTNAME]

show_emailaddress:

name: E-mailaddress sort_column: UserPeer::EMAILADDRESS

As you can see the fullname-column contains a join_fields option which defines the foreign-key relationship. In the emailaddress-column this now is obsolete but you can define it twice if you like. Furthermore the emailaddress-column only sorts on one foreign-column, called emailaddress, while the fullname-column gets sorted on 3 foreign-columns.

If your table would contain the columns itself, but you use a partial to show something like fullname you also don't need to specify the join_fields-option.

If you don't specify the sort_column-option the column simply still can't be sorted just like currently is the case for partials and foreign columns.

I've altered the action.class.php and _list_th_tabular.php files from the theme.

---

the change in _list_th_tabular.php :

...

<?php $sort_column = $this->getParameterValue('list.fields.'.$column->getName().'.sort_column'); ?>

<?php if ( ($column->isReal()) ($sort_column) ): ?>

...

---

and the changes in action.class.php are:

for the function executeList(): (adding the join(s) to the criteria)

...

$c = new Criteria();

<?php if ($fields = $this->getParameterValue('list.fields')): ?> <?php foreach ($fields as $key => $field): ?> <?php if ($join_fields= $this->getParameterValue('list.fields.'.$key.'.join_fields')): ?>

$c->addJoin(<?=$join_fields[0]?>,<?=$join_fields[1]?>);

<?php endif; ?> <?php endforeach; ?> <?php endif; ?>

$this->addSortCriteria($c);

...

the function addSortCriteria() is almost completely changed:

protected function addSortCriteria($c) {

if ($sort_column = $this->getUser()->getAttribute('sort', null, 'sf_admin/<?php echo $this->getSingularName() ?>/sort')) {

switch ($sort_column) {

<?php if ($fields = $this->getParameterValue('list.fields')): ?> <?php foreach ($fields as $key => $field): ?> <?php if ($this->getParameterValue('list.fields.'.$key.'.sort_column')): ?>

case '<?=$key?>':

<?php $column = $this->getParameterValue('list.fields.'.$key.'.sort_column');?>

<?php if ( is_array($column) ) : ?>

$sort_columns = Array(<?php

foreach ($column as $c) {

echo $c; if (next($column)) echo ", ";

} ?>);

<?php else: ?>

$sort_columns = Array(<?= $column ?>);

<?php endif; ?>

break;

<?php endif; ?> <?php endforeach; ?> <?php endif; ?>

default:

$sort_columns = Array(<?php echo $this->getClassName() ?>Peer::translateFieldName($sort_column, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME)); break;

}

foreach ($sort_columns as $sort_column) {

if ($this->getUser()->getAttribute('type', null, 'sf_admin/<?php echo $this->getSingularName() ?>/sort') == 'asc') {

$c->addAscendingOrderByColumn($sort_column);

} else {

$c->addDescendingOrderByColumn($sort_column);

}

}

}

}

---

I'm going to add an other option, to also have the ability to sort on multiple fields! this will become an other ticket, but will continue on this base...

I'll attach both files

I have a system in which there is an optional foreign key column. When used with this template, if the column is null, then that record is not displayed. The problem is the default INNER JOIN used. I've modified the template to allow specifying the join type using an additional key in the YAML, join_type. I've only tested it with left joins; I'm not sure it makes sense using any other join type. Inner join is clearly wrong in many circumstances, but could be a useful speed optimization in certain cases, so perhaps left join should be made the default in the future.

I'll attach a diff.