How to join user table with another table in views data

One of the most awesome features of Drupal is probably the views module which has been integrated into the core on Drupal 8. Views deals perfectly well with entities and it is basically quite simple to pass data to it when you just work with a database table. 

To pass a table to views and get the data as fields you'll have to use hook_views_data() which you should put in a file called "your_module.views.inc" at the root of your module folder. My issue was to join the user table with a custom table I had made for a custom module and I'm going to show you how I finally get it working. Let say we have a really simple table called custom_table which contains custom data for users :

What we want to achieve is to load these pieces of information in a view and link the user uid to the full user object so we can, for example, return the username and its email address in the view. Firstly, we implement our table, the fields related to it (without the user's one) and the link to the user database.

/**
 * Implements hook_views_data().
 */
function your_module_views_data() {

  $data['custom_table'] = [
    'table' => [
      'group' => t('Your module'),
      'provider' => 'your_module', // That tells drupal which module provide the database schema.
      'base' => [
        // Define the base table to use.
        'field' => 'id', // Tells drupal which field will be our key.
        'title' => t('Custom table'),
        'help' => t('This table contains datas related to users'),
      ],
      'join' => [ // We join the user database so we can use it after.
        'users' => [ // In D8 users just contain the uid and the uuid so it's really simple, but the trick comes after.
          // The primary key in users table.
          'left_field' => 'uid', // The field we have to link from, from users table
          // The foreign key in custom_table,
          'field' => 'uid', // The field we have to link to, from custom_table
        ],
      ],
    ],
    // Now we can load our fields so we'll get them into views
    'id' => [
      'title' => t('ID'),
      'help' => t('The unique ID.'),
      'field' => [
        'id' => 'numeric',
      ],
      'filter' => [
        'id' => 'numeric',
      ],
      'sort' => [
        'id' => 'standard',
      ],
      'argument' => [
        'id' => 'numeric',
      ],
    ],
    'data_1' => [
      'title' => t('Data 1'),
      'help' => t('Some data.'),
      'field' => [
        'id' => 'standard',
      ],
      'filter' => [
        'id' => 'string',
      ],
      'argument' => [
        'id' => 'string',
      ],
    ],
    'data_2' => [
      'title' => t('Data 2'),
      'help' => t('Other data.'),
      'field' => [
        'id' => 'standard',
      ],
      'filter' => [
        'id' => 'string',
      ],
      'argument' => [
        'id' => 'string',
      ],
    ],
 ];

 return $data;
}

So from here, you should be able to see your table in the view settings and the field coming in the view page :

What we want to achieve now is to link our uid to a full user object. The difference between Drupal 7 and Drupal 8 is that users infos are stored in the user_field_data table and not in the users one anymore. But we do have joined the "users" table in the code above right? Yes, that is all the trick, and that's what made me struggle with, so that's why I'm sharing it with you. Actually, even if we linked the users table which is almost empty, views will deal with it as user entities, so it knows that data are stored in user_field_table and will load it with the join we made.

We can now join our users and see how to get a full user object :

'uid' => [
      'title' => t('User'),
      'help' => t('The full user object.'),
      'relationship' => [
        'group' => t('Users'),
        'label' => t('The user from custom table'),
        'title' => t('Custom table user'),
        'help' => t('Display user informations of custom table user.'),
        'base' => 'users_field_data', // The trick is here and reside in calling the users_field_data table even if we didn't join it. Why ? because if we link the users table here we will just get a partial user object and not a fully loaded one. If you want the username, you'll have to search for it into users_field_data ;)
        'base field' => 'uid', //database field to join on
        'relationship field' => 'uid', //relationship to join with
        'id' => 'standard',
      ],
    ],

Now you just add the relationship we just made and you should find all the user fields in your view.

Finally the complete code snippet :

/**
 * Implements hook_views_data().
 */
function your_module_views_data() {

$data['custom_table'] = [
    'table' => [
      'group' => t('Your module'),
      'provider' => 'your_module',
      'base' => [
        'field' => 'id',
        'title' => t('Custom table'),
        'help' => t('This table contains datas related to users'),
      ],
      'join' => [
        'users' => [
          'left_field' => 'uid', 
          'field' => 'uid', 
        ],
      ],
    ],
    // Defines custom table to views.
    'id' => [
      'title' => t('ID'),
      'help' => t('The unique ID.'),
      'field' => [
        'id' => 'numeric',
      ],
      'filter' => [
        'id' => 'numeric',
      ],
      'sort' => [
        'id' => 'standard',
      ],
      'argument' => [
        'id' => 'numeric',
      ],
    ],
    'data_1' => [
      'title' => t('Data 1'),
      'help' => t('Some data.'),
      'field' => [
        'id' => 'standard',
      ],
      'filter' => [
        'id' => 'string',
      ],
      'argument' => [
        'id' => 'string',
      ],
    ],
    'data_2' => [
      'title' => t('Data 2'),
      'help' => t('Other data.'),
      'field' => [
        'id' => 'standard',
      ],
      'filter' => [
        'id' => 'string',
      ],
      'argument' => [
        'id' => 'string',
      ],
    ],
    'uid' => [
      'title' => t('User'),
      'help' => t('The full user object.'),
      'relationship' => [
        'group' => t('Users'),
        'label' => t('The user from custom table'),
        'title' => t('Custom table user'),
        'help' => t('Display user informations of custom table user.'),
        'base' => 'users_field_data', // database table
        'base field' => 'uid', //database field to join on
        'relationship field' => 'uid', //relationship to join with
        'id' => 'standard',
      ],
    ],
 ];

  return $data;
}