How to add subquery in views to remove particular type of rows?

Question

I have applied join to users table with node_revision table like below:

function custom_overrides_views_data_alter(&$data) {

  $data['users']['node_revision'] = array(
    'title' => t('Users revisions authored'),
    'help' => t('Relate a user to their revisions'),
    'relationship' => array(
      'base' => 'node_revision',
      'base field' => 'uid',
      'relationship field' => 'uid',
      'handler' => 'views_handler_relationship',
      'label' => t('revisions bubbla'),
    ),
  );
}

And by creating view now I got number of revisions per user like below:

Westerberg (15752)

Eriksson (4305)

Jakob (4182)

Andersson (4153)

But now I want to remove those revisions which are created when user created node first time ie: will only show edits but not the revisions which are created first time when new node was created. To solve this logic is to remove nodes having below true:

excluding the “first time created” nodes (original node revision)

node.created = node_revision.timestamp 

I tried using query alter but it uses quotes around field please suggest how to remove that ?

if ($view->name == "my_view") {

    $sub_query = db_select('node', 'n')
      ->fields('n', array('created'))
      ->condition('n.created', 'node_revision_users.timestamp');


    $view->query->where[1]['conditions'][] = array(
      'field' => 'node_revision_users.timestamp',
      'value' => $sub_query,
      'operator' => 'NOT IN'
    );

  }

Output problem is like screenshot:

Please suggest! thanks

Answer 1

You should use where() to match table columns instead of condition

if ($view->name == "my_view") {
    $sub_query = db_select('node', 'n')
      ->fields('n', array('created'))
      ->where("n.created = node_revision_users.timestamp");

      $view->query->where[1]['conditions'][] = array(
        'field' => 'node_revision_users.timestamp',
        'value' => $sub_query,
        'operator' => 'NOT IN'
      );
}

Update

Why where() not condition()

Syntax for condition:

$query->condition($field, $value = NULL, $operator = '=')

shows condition() is for building standard condition fragement

Syntax for where:

$query->where($snippet, $args = array())

shows where() is for building any arbitrary SQL as conditional fragment.

$snippet may contain any legal SQL fragment.