2

I'm creating a module that retrieves Youtube data (video and playlists) from Youtube feeds

I've created cutom tables :

  • youtube_video (with fields : vid, vtitle, description...)
  • youtube_playlist (with fields : pid, ptitle, description...)
  • youtube_video_playlist (with fields: vid, pid, weight)

I'd like to have a view that displays all videos from a playlist (filtered with a contextual filter). I'm using Views 7.x-3.8 and Drupal 7. It seems that what i need here requires quite some custom coding, but i'm fairly new to Views programming...

How can i set relationships so that i can access to all the fields i need ( youtube_video.vtitle, youtube_playlist.ptitle, youtube_playlist.vid) to construct my query ?

I've looked at the example in the view api with hook_views_data() to add a custom table (youtube_playlist) in Views to put in relation with (youtube_video), but here, i'm using a third table (the relation table youtube_video_playlist).

So in "sql terms" how can i have :

SELECT v.vtitle, v.vid 
FROM youtube_video AS v 
JOIN youtube_video_playlist AS vp
    ON vp.vid = v.vid
JOIN youtube_playlist AS p
    ON p.pid = vp.pid
WHERE p.pid = contextual_value_of_playlist_pid

So in fact, since i'm learning, in this particular case :

  • Using all the hooks, classes in the Views API, how can i achieve my goal (hooks / classes to use) ?
  • is it useful (= optimized) to go all the way in using the views api or could i set this custom request above directly in the code ?

I've also created entities from these 2 main tables (youtube_video and youtube_playlist), in order to display them "easily" in views. But is this (creating entities from tables) mandatory to my purpose (displaying videos from some playlist) ?


@Andy : Thank you for this specific help : I didn't find it in the "regular" documentation, but in this advanced help, there's an explanation for the indirect joins. That said, i didn't manage to display my fields :

I tried first the example in the documentation and it works as expected,i've got a group showing in the different section of views (add field, add filter, add sort...), but when i try to adapt the code in the advanced help, with the left table, nothing is displayed :

function mymodule_map_views_data() {
  //The group name
  $data['youtube_playlist']['table']['group'] = t('Youtube playlist');
  $data['youtube_video']['table']['group'] = t('Youtube video');
  //The base table
  $data['youtube_video']['table']['base'] = array(
    'field' => 'vid',
    'title' => t('youtube video'),
    'help' => t('youtube video should be related to youtube playlist.'),
    'weight' => -10,
    'defaults' => array(
      'field' => 'vtitle',
    ),
  );
  //The join
  $data['youtube_playlist']['table']['join']['youtube_video'] = array(
    'left_table' => 'youtube_video_playlist',
    'left_field' => 'pid',
    'field' => 'pid',
  );
  // Allow this field to be used as a contextual filter.
  $data['youtube_playlist']['pid'] = array(
    'title' => t('playlist id'),
    'help' => t('playlist id'),
    'field' => array(
      'handler' => 'views_handler_field_numeric',
      'click sortable' => TRUE,
    ),
    'argument' => array(
      'handler' => 'views_handler_argument',
      'name field' => 'title',
    ),
  );
  $data['youtube_video_playlist']['table']['join']['youtube_video'] = array(
    'left_field' => 'vid',
    'field' => 'vid',
  );
  //the fields description
  $data['youtube_playlist']['ptitle'] = array(
    'title' => t('youtube playlist title'),
    'help' => t('youtube playlist title.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE, 
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  //the fields description
  $data['youtube_playlist']['description'] = array(
    'title' => t('youtube playlist description'),
    'help' => t('youtube playlist description.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE, 
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  $data['youtube_video']['vtitle'] = array(
    'title' => t('youtube video title'),
    'help' => t('youtube video title.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE, 
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  $data['youtube_video']['vhash'] = array(
    'title' => t('youtube video vhash'),
    'help' => t('youtube video vhash.'),
    'field' => array(
      'handler' => 'views_handler_field',
      'click sortable' => TRUE, 
    ),
    'sort' => array(
      'handler' => 'views_handler_sort',
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_string',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_string',
    ),
  );
  return $data;
}

I think i'm confused with the left table / base table and which one should be fully described.

I also add the scheme, just in case :

function MYMODULE_schema() {
  $schema['youtube_playlist'] = array(
    'description' => t('List of youtube playlists from a channel'),
    'fields' => array(
      'pid' => array(
        'description' => t('Unique key for playlist'),
        'type' => 'serial',
        'size' => 'medium',
        'unsigned' => true,
        'not null' => true,
      ),
      'ptitle' => array(
        'description' => t('video title'),
        'type' => 'varchar',
        'length' => '255',
        'not null' => true,
      ),
      ...
    ),
    'primary key' => array('pid'),
  );
  $schema['youtube_video_playlist'] = array(
    'description' => t('Video and playlist'),
    'fields' => array(
      'vid' => array(
        'description' => t('Primary Key: Identifier for a Youtube video'),
        'type' => 'int',
        'size' => 'medium',
        'unsigned' => true,
        'not null' => true,
      ),
      'pid' => array(
        'description' => t('Unique key for playlist'),
        'type' => 'int',
        'size' => 'medium',
        'unsigned' => true,
        'not null' => true,
      ),
      'position' => array(
        'description' => t('Position of the video in the playlist'),
        'type' => 'int',
        'size' => 'small',
        'unsigned' => true,
        'not null' => true,
      ),
    ),
    'primary key' => array('vid', 'pid'),
  );
  $schema['youtube_video'] = array(
    'description' => t('Informations on video from Youtube'),
    'fields' => array(
      'vid' => array(
        'description' => t('Unique key for video'),
        'type' => 'serial',
        'size' => 'medium',
        'unsigned' => true,
        'not null' => true,
      ),
      'vtitle' => array(
        'description' => t('video title'),
        'type' => 'varchar',
        'length' => '255',
        'not null' => true,
      ),
    ),
    ...
    'placeid' => array(
        'description' => t('id for the place'),
        'type' => 'int',
        'size' => 'medium',
        'unsigned' => true,
    ),
    'primary key' => array('vid'),
    'foreign keys' => array(
      'placeid' => array(
        'table' => 'youtube_places',
        'columns' => array('placeid' => 'placeid'),
      ),
    ),
  );
  return $schema;     
}
  • If you haven't already, I'd suggest downloading advanced_help and looking at Describing tables to Views. – Andy Jul 22 '14 at 14:04
  • I think the problem is in the 'join' part : if i leave the rest and change the join to, say, 'node' as a direct join, like in the regular documentation, the group is displayed. but the use of the "left_table" and such mess up everything. Maybe i'm forgetting something – Overdose Jul 23 '14 at 10:12
  • It's really hard to read your hook_views_data() as it stands. Could you possibly indent it properly, and order it by tables? – Andy Jul 23 '14 at 13:46
  • i reindented the code – Overdose Jul 23 '14 at 18:33
  • Thanks. $data['youtube_playlist']['pid'] is incomplete. I only described the argument key of it in my answer, but it should also be defined like a normal field. – Andy Jul 23 '14 at 19:45
3

The best place to find documentation for these things is actually the code in the views module.

If you look at node_views_data function you can see an example of creating a relationship from a node's author (uid) to the user table.

function node_views_data() {
  ..
  // uid field
  $data['node']['uid'] = array(
    'title' => t('Author uid'),
    'help' => t('The user authoring the content. If you need more fields than the uid add the content: author relationship'),
    'relationship' => array(
      'title' => t('Author'),
      'help' => t('Relate content to the user who created it.'),
      'handler' => 'views_handler_relationship',
      'base' => 'users',
      'field' => 'uid',
      'label' => t('author'),
    ),
    'filter' => array(
      'handler' => 'views_handler_filter_user_name',
    ),
    'argument' => array(
      'handler' => 'views_handler_argument_numeric',
    ),
    'field' => array(
      'handler' => 'views_handler_field_user',
    ),
  );
  ..
}

The interesting part here is the 'relationship' part. Basically you define the table (base) that you are joining and the column (field) you are joining on. In your case where you are joining to another FK, you can use the built in handler from views.

So in code you would need to do something like this:

$data['youtube_video']['vid'] = array(
  'relationship' => array(
    'handler' => 'views_handler_relationship',
    'base' => 'youtube_video_playlist',
    'field' => 'vid',
    ..
  ),
  ...
);

$data['youtube_video_playlist']['pid'] = array(
  'relationship' => array(
    'handler' => 'views_handler_relationship',
    'base' => 'youtube_playlist',
    'field' => 'pid',
    ..
  ),
  ...
);

In the Views UI you would create two relationships.

  • First of all, thanks for giving me pointers. But i am still very confused : I've updated my question with your code, but the group 'youtube video' / field 'titlev' still doesn't show. What do you think of the join ? (I think this is the culprit but i can't find out the correct syntax : there're no error thrown but nothing shown). I've seen example with left_table for instance in profile.views.inc in Views, but i can't see what is wrong here. – Overdose Jul 23 '14 at 11:45
  • @overdose The join is a separate way of linking the tables to using relationships. You can use one or both of them. – Andy Jul 23 '14 at 12:50
  • @andy : so, my updated function, with googletorp update, is syntaxically correct ? I haven't to "choose" between the 'join' index or the 'relationship' ? I can write both (which i tried, and separately also) and there's no problem ? – Overdose Jul 23 '14 at 13:04
  • First things first : if I want to display all video from a playlist with a similar query as i put in my message, should the 'base' table be 'youtube_video' or 'youtube_playlist' ? I put youtube_video, but I'm not sure of my left relation anymore :D – Overdose Jul 23 '14 at 13:12
  • @overdose You're right, the base table should be youtube_video. – Andy Jul 23 '14 at 13:20
2

If you wanted to use a join rather than a relationship you need something like this:

$data['youtube_playlist']['table']['join']['youtube_video'] = array(
  'left_table' => 'youtube_video_playlist',
  'left_field' => 'pid',
  'field' => 'pid',
);

$data['youtube_playlist']['pid'] = array(
  ...
  // Allow this field to be used as a contextual filter.
  'argument' => array(
    'handler' => 'views_handler_argument',
    'name field' => 'ptitle',
  ),
);

$data['youtube_video_playlist']['table']['join']['youtube_video'] = array(
  'left_field' => 'vid',
  'field' => 'vid',
);

Relationships are more flexible, this approach is appropriate if the tables are always joined in this way.

  • Thanks for the update and the time you spend on my problem. OK, for the base table. I updated my code with yours, but still no luck. FYI : I don't "want" 'join' over 'relationship', i used first join because i saw it in the doc example. If relationship if more flexible, i don't mind using it :D So frustrating since everything you gave me seems ok in other cases (in doc). I even tested this on a fresh Drupal install, just in case :D. Why is there no error thrown when you need them :D – Overdose Jul 23 '14 at 16:56
  • I also updated my post with (the relevant fields of) the schema in the .install. Just in case, there's something wrong. But pid, vid are primary keys, so i don't think so. – Overdose Jul 23 '14 at 17:18

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.