How do I sort by Case Type Title instead of id in a Drupal View?

Question

We frequently need to sort Drupal Views by Case Type.

It works, but sorts by id not the title field.

I believe it has to do with the pseudoconstant references in civicrm.case.inc and civicrm.core.inc.

I think that approach is fine except when it's time to sort and/or filter.

In Drupal Views, all exposed filters are sorted by id. So when a user tries to filter by Case Type they get an unordered list of Case Types that to their eyes are in a very random order and is more difficult to use.

Same thing if I sort the overall view by case type. Cases appear in non-alphabetical order and require a bit more concentration to digest them.

Is there a way to sort these fields in alphabetical order?

Answer 1

Have you tried Civicrm Entities in Drupal? It should allow you to expose anything as a drupal entity but I haven't verified this case.

Answer 2

I had a similar problem on a non-civi drupal site (since this is a general drupal views problem). I worked around it with this inelegant junk in my custom module:

function mymodule_views_pre_render($view) { if ($view->name == 'the_name_of_the_view') { foreach ($view->result as $i => $r) { // Might not need to loop - idea is to do your own custom sorting and put result into some $new_result variable } $view->result = $new_result; } }

Note it's drupal 6. Might have to fiddle for drupal 7. You may want to use your favorite technique to examine $view->result to see what it looks like first.

Answer 3

I think this will require a patch to the CiviCRM Views code. If you can offer to do so, you could open a ticket in JIRA and supply the fix

Answer 4

The simplest solution of all. As mentioned here: Civicase Dashboard - reordering case types

I changed the weights in the table to easily change the order.

Why the heck didn't I think of that?

It would be nice to be able to change the weight within the Case Type UI.

Update While the weight solution worked nicely for the select lists in Views exposed filters, it would not sort the results by case type name only by id.

For example, a view listing case types came out like this:
Case Type C
Case Type E
Case Type A
Case Type D
Case Type B

The issue appears to be that the civicrm_case_type table is never exposed to views.

So I added this to civicrm.case.inc:

//----------------------------------------------------------------
  // CIVICRM Case Type Names are here, base tabling it up.
  //----------------------------------------------------------------

  $data['civicrm_case_type']['table']['group'] = t('CiviCRM Case Type Names');

  $data['civicrm_case_type']['table']['base'] = array(
    // Governs the whole mozilla
    'field' => 'id',
    'title' => t('CiviCRM Case Type Names'),
    'help' => t("View displays CiviCRM Case Type Names"),
  );

  // Explain how this table joins to others.
  $data['civicrm_case_type']['table']['join'] = array(
    // Directly links to case table.
    'civicrm_case' => array(
      'left_field' => 'case_type_id',
      'field' => 'id',
    ),
  );

  //CiviCRM Case Type - FIELDS

  //Case Type Title
  $data['civicrm_case_type']['title'] = array(
    'title' => t('Case Type Name'),
    'real field' => 'title',
    'help' => t('The Name of the Case Type'),
    'field' => array(
    'handler' => 'views_handler_field',
    'click sortable' => TRUE,
    ),
    'argument' => array(
     'handler' => 'views_handler_argument',
    ),
    'sort' => array(
    'handler' => 'views_handler_sort',
    ),
  );

Which joins the table and only exposes the title field to Views.

After I applied this, the same view printed out:
Case Type A
Case Type B
Case Type C
Case Type D
Case Type E