sub_query join drupal7 view_query_alter

views里面添加 表达式 expression
https://www.drupal.org/docs/7/api/database-api/dynamic-queries/expressions

select max 最大值
https://blog.csdn.net/xx_star1204/article/details/88187631

https://www.drupal.org/forum/support/module-development-and-code-questions/2009-03-22/implementing-custom-sql-query-for



$sub_query = db_select('workflow_node_history', 'sub_workflow_node_history_states');
    $sub_query->addField('sub_workflow_node_history_states', 'nid');
    $sub_query->addField('sub_workflow_states', 'weight', 'weight');
    $sub_query->addField('sub_workflow_states_old', 'weight' , 'old_weight');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states', 'sub_workflow_node_history_states.sid = sub_workflow_states.sid');
    $sub_query->addJoin('LEFT', 'workflow_states', 'sub_workflow_states_old', 'sub_workflow_node_history_states.old_sid = sub_workflow_states_old.sid');
    $sub_query->orderBy('sub_workflow_node_history_states.hid', 'DESC');

    $join = new views_join();
    $join->definition = array('table formula' => $sub_query, 'left_field' => 'nid', 'field' => 'nid', 'left_table' => 'node');
    $join->left_table = 'node';// or wathever table you want to join with
    $join->field = 'nid';
    $join->left_field = 'nid';// 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
    $this->query->table_queue['workflow_node_history_states'] = array (
      'alias' => 'workflow_node_history_states',// I believe this is optional
      'table' => $sub_query,
      'relationship' => 'node',
      'join' => $join,
    );