0

Is there any possibility to add a relationship in views from a second database to the default one. I.e. I am using a second database which contains one table with a userid. In my hook_views_data I would like to add a relationship from this field to users_field_data uid. How can I do this?

  • Did you define join table on hook_view_data? – Jonh Jul 17 '18 at 9:01
  • yes i did and included 'table' => 'default' . '.users_field_data' in the join – Sebastion Jul 17 '18 at 9:18
  • And did you add field relationship on views settings? – Jonh Jul 17 '18 at 9:40
  • Go to admin/structure/views/settings and check to Show the SQL query. This setting will show sql query. It's better for debug. And Can you provide your code in function hook_view_data() and your views? – Jonh Jul 17 '18 at 13:02
0

I was logged out. Yes I tried to add field relationship on views settings, but my joined table was not shown. Consider that I want to join from a view showing my table to the users_field_data table, not vice versa.

my view is simply showing the the fields from my custom table. This works. But I can't add a relationship to the second db. The SQL query is

SELECT my_tracking.id AS id, my_tracking.uid AS my_tracking_uid, my_tracking.event_id AS my_tracking_event_id
FROM {my_tracking} my_tracking
ORDER BY id ASC
LIMIT 25 OFFSET 0

my hook_views_data looks like

$data['my_tracking']['table']['name'] = 'event_tracking' . '.my_tracking';

$data['my_tracking']['table']['group'] = t('Event Tracker');

$data['my_tracking']['table']['base'] = array(
  'field' => 'id',
  'title' => t('Event Tracker'),
  'help'  => t('Contains data about tracking events.'),
  'database' => 'event_tracking',
);

$data['my_tracking']['table']['join'] = array(
  'users_field_data' => array(
    'table' => 'default' . '.users_field_data',
    'left_field' => 'uid',
    'field'      => 'uid',
  ),
);

$data['my_tracking']['table']['provider'] = 'my_tracking';


$data['my_tracking']['id'] = array(
  'title' => t('Record ID'),
  'field' => array(
    'id' => 'numeric',
  ),
  'sort' => array(
    'id' => 'standard',
  ),
  'filter' => array(
    'title' => t('Record ID'),
    'id' => 'numeric',
  ),
  'argument' => array(
    'id' => 'numberic',
  ),
);

$data['my_tracking']['uid'] = array(
  'title' => t('UID'),
  'help'  => t("The user's UID."),
  'field' => array(
    'id' => 'numeric',
  ),
  'sort' => array(
    'id' => 'standard',
  ),
  'filter' => array(
    'title' => t('UID'),
    'id' => 'numeric',
  ),
  /*  'relationship' => array(
    'title'              => t('User who is tracked'),
    'help'               => t('The user associated with the record.'),
    'id'                 => 'standard',
    'base'               => 'users_field_data',
    'base field'         => 'uid',
    'field'              => 'uid',
    'label'              => t('The who is tracked'),
  ),*/
  'argument' => array(
    'id' => 'numeric',
  ),
);

$data['my_tracking']['event_id'] = array(
  'title' => t('Event ID'),
  'field' => array(
    'id' => 'numeric',
  ),
  'sort' => array(
    'id' => 'standard',
  ),
  'filter' => array(
    'title' => t('Event ID'),
    'id' => 'numeric',
  ),
  'argument' => array(
    'id' => 'numberic',
  ),
);

No relationship can be added in views. Previously when I used a single database I was able to add the relationship by using the relatationship handler that is commented out above now. I don't know how to adapt it to 2 databases.

  • Do you have one or two table? In your code, I see only one table my_tracking. – Jonh Jul 17 '18 at 13:44
  • only one table. I can show this table in views correctly. Now I want to join to user_field_data table – Sebastian Jul 17 '18 at 13:48
  • You can try comment array $data['my_tracking']['table']['join']. And uncomment relationship on $data['my_tracking']['uid']. Add relation to User The user associated with the record. Remember clear cache after edit hook_view_data – Jonh Jul 17 '18 at 14:08
  • Already tried, doesn't work as the table users_field_data is in the default (main) database and can't be found by views. I also tried to prefix it 'base' => 'my_main_db.users_field_data' but this doesn't work as views builds aliases and a dot (.) is not accepted. If I output the query: ... LEFT JOIN my_main_db.users_field_data my_main_db.users_field_data_my_tracking ON... – Sebastian Jul 17 '18 at 14:24
  • Don't add prefix, just define exactly table name. Try remove some line $data['my_tracking']['table']['name'] = 'event_tracking' . '.my_tracking'; 'database' => 'event_tracking',. Or just copy code in dblog.views.inc and replace watchdog with your table name. It's good example. – Jonh Jul 17 '18 at 14:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.