Sorting by user custom fields in an administrator list view.

[Steven Berkson]

Can anyone offer any tips on accomplishing being able to sort by com_user custom fields?

 

I have a custom component table #__mycomponent with a ‘user_id’ field that attaches each record to a user.

I have a need to sort the users by last name, first name. I have custom user fields for first name and last name.

 

In my list view the query starts with…

 

$query->from($db->quoteName('#__ mycomponent,'m'));

              //Get reservation user Information

              $query->select('u.name, u.email')

                            ->join('LEFT', $db->quoteName('#__users','u'),$db->quoteName('m.user_id').' = '.$db->quoteName('u.id'));

 

And later…

              // Join Custom Fields of User

              // Get the user fields in an array of name/id

              $getFields = FieldsHelper::getFields('com_users.user', array());

              $fields = ArrayHelper::pivot($getFields , 'name');

              $query->select($db->quoteName('f.value', 'fname'))

                            ->join(

                                           'LEFT',

                                           $db->quoteName('#__fields_values', 'f') . ' ON (' . $fields['fname']->id . ' = ' . $db->quoteName('f.field_id'). ') AND (' . $db->quoteName('m.user_id') . ' = ' . $db->quoteName('f.item_id'). ')'

                             );

                            

                             $query->select($db->quoteName('f2.value', 'lname'))

                             ->join(

                                           'LEFT',

                                           $db->quoteName('#__fields_values', 'f2') . ' ON (' . $fields['lname']->id . ' = ' . $db->quoteName('f2.field_id'). ') AND (' . $db->quoteName('m.user_id') . ' = ' . $db->quoteName('f2.item_id'). ')'

                             );

 

As a test, I made the sort link:

<?php echo HTMLHelper::_('searchtools.sort', 'COM_ MYCOMPONENT_TABLE_TABLEHEAD_NAME', 'f2.lname', $listDirn, $listOrder); ?>

 

The output in list view is fine, but just attempting to sort by last name rather than user name or another field comes out with a ton of missing values in the query, like I get a list view with no names of any kind and a bunch of missing information ,but if I click to edit a record, all the information is there, so something just breaks in the list query.

 

Any idea on how I can make my list view for my table, with associated user by user_id, sortable based on custom fields of com_user  first and last name?

 

Any ideas would be appreciated.

                            

 

 

[Neo314]

I think nevermind...

I changed "f2.lname" to just lname, and added "'lname', 'lname'," to the filter fields and it worked fine with a number of orphaned records confusing my results.

Is there and easy way to concatenate fields in the listview model so the ordering clause for name will result in sorting by lname." ".fname?

[Neo314]

I resolved sorting by last name, first name with a nested query, and then sorting on the field sortname.

$query->select($db->quoteName('f.value', 'fname'));
$query->select($db->quoteName('f2.value', 'lname'));
$query->select($query->concatenate($db->quoteName(array("f2.value", "f.value")), " ") . " AS sortname")
->join(
'LEFT',

$db->quoteName('#__fields_values', 'f') . ' ON (' . $fields['fname']->id . ' = ' . $db->quoteName('f.field_id'). ') AND (' . $db->quoteName('m.user_id') . ' = ' . $db->quoteName('f.item_id'). ')'
)

->join(
'LEFT',
$db->quoteName('#__fields_values', 'f2') . ' ON (' . $fields['lname']->id . ' = ' . $db->quoteName('f2.field_id'). ') AND (' . $db->quoteName('m.user_id') . ' = ' . $db->quoteName('f2.item_id'). ')'
);