Drupal - how to use hook_views_query_alter() to modify where condition?

To add a new where clause you can use, add_where

For example :

$query->add_where(1,'taxonomy_term_data_node.tid', $value, 'NOT IN');

You can access the content of a where condition modifying its value:

$query->where[0]['conditions'][0]['value'] = 'something...';

Pretty similar what you did with orderby. Also you can add custom where conditions (https://api.drupal.org/api/views/plugins%21views_plugin_query_default.inc/function/views_plugin_query_default%3A%3Aadd_where/7 and https://api.drupal.org/api/views/plugins%21views_plugin_query_default.inc/function/views_plugin_query_default%3A%3Aadd_where_expression/7)

function mymodule_views_query_alter(&$view, &$query) {
  // Term id's to unset from result set.
  $tids = array(346,355,359);
  if ($view->name == 'yourviewname') {

    $query->add_where(1,'taxonomy_term_data_node.tid', $tids, 'NOT IN');
    $query->orderby[0]['field'] = "CASE WHEN taxonomy_term_data_taxonomy_term_hierarchy.weight IS NULL THEN taxonomy_term_data_node.weight ELSE taxonomy_term_data_taxonomy_term_hierarchy.weight END";
    $query->orderby[0]['direction'] = "ASC";
    $query->orderby[1]['field'] = "taxonomy_term_data_node.weight";
    $query->orderby[1]['direction'] = "ASC";
  }
}