2

In my project, I want do display users information(fields) by user role. I created a view for users, and I used a subquery for sorting user field by ascending order with the following query.

SELECT field_level_of_work_value, FROM `field_data_field_level_of_work` ORDER BY FIELD(field_level_of_work_value, '0','1','2','3'), field_level_of_work_value ASC

I don't know how to use this query in hook_view_query_alter(). This is the code I am using.

function user_worklevel_sorting_views_query_alter(&$view, &$query) {
  if ($view->name=="attorney_workload") {
    $sub_query = db_select('node','node');
    $sub_query->addField('field_data_field_level_of_work', 'value','field_level_of_work_value');
    $sub_query->addField('users', 'uid','uid');

    $sub_query->leftJoin('field_data_field_level_of_work', 'field_data_field_level_of_work', 'field_data_field_level_of_work.entity_id=users.uid');
    $sub_query->orderBy('wl.field_level_of_work_value' , '0')
      ->orderBy('wl.field_level_of_work_value' ,'1')
      ->orderBy('wl.field_level_of_work_value' , '2')
      ->orderBy('wl.field_level_of_work_value' ,'3')
      ->orderBy('wl.field_level_of_work_value','ASC');

    $join = new views_join();
    $join->definition = array('table formula' => $sub_query, 'left_field' => 'uid', 'field' => 'uid', 'left_table' => 'node');
    $join->left_table = 'users';// or wathever table you want to join with
    $join->field = 'uid';
    $join->left_field = 'uid';// or wathever column you want to join with
    $join->type = 'LEFT';// if you want another join type play with this value

    // Do the actual join
    $query->table_queue['field_data_field_level_of_work'] = array(
      'alias' => 'field_data_field_level_of_work',// I believe this is optional
      'table' => $sub_query,
      'relationship' => 'node',
      'join' => $join,
    );
  }
}

screenshot

screenshot

I don't want to display null values in "Level of work" column and it would be display like my custom orderBy ASC.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.