I want to create blocks to display a list of all users with their number of comments and number of post on forum.
In SQL, something like that :
SELECT u.name as user, COUNT( n1.nid ) as 'number of post', COUNT( n2.nid ) as 'number of comment', COUNT( c.cid ) as 'total'
FROM users u
LEFT JOIN comment c ON u.uid = c.uid
LEFT JOIN users_roles ur ON u.uid = ur.uid
LEFT JOIN ( SELECT n.nid, n.type FROM node n WHERE n.type = 'forum' ) n1 ON c.nid = n1.nid
LEFT JOIN ( SELECT n.nid, n.type FROM node n WHERE n.type <> 'forum' ) n2 ON c.nid = n2.nid
WHERE u.uid <> 0 AND ur.rid IS NULL
GROUP BY u.name
Using views I'm using this patch to have relationship between users and comments and filters to test if the node type related to comment is 'forum' or not.
There is an export of my display on views :
<?php
$view = new view();
$view->name = 'utilisateurs';
$view->description = 'liste les utilisateurs du site';
$view->tag = 'default';
$view->base_table = 'users';
$view->human_name = 'utilisateurs';
$view->core = 7;
$view->api_version = '3.0';
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
/* Display: Master */
$handler = $view->new_display('default', 'Master', 'default');
$handler->display->display_options['title'] = 'utilisateurs';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['use_more_text'] = 'plus';
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'perm';
$handler->display->display_options['access']['perm'] = 'access user profiles';
$handler->display->display_options['cache']['type'] = 'none';
$handler->display->display_options['query']['type'] = 'views_query';
$handler->display->display_options['exposed_form']['type'] = 'basic';
$handler->display->display_options['exposed_form']['options']['submit_button'] = 'Appliquer';
$handler->display->display_options['exposed_form']['options']['reset_button_label'] = 'Réinitialiser';
$handler->display->display_options['exposed_form']['options']['exposed_sorts_label'] = 'Trier par';
$handler->display->display_options['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: comments and posts */
$handler->display->display_options['relationships']['uid_comment']['id'] = 'uid_comment';
$handler->display->display_options['relationships']['uid_comment']['table'] = 'users';
$handler->display->display_options['relationships']['uid_comment']['field'] = 'uid_comment';
$handler->display->display_options['relationships']['uid_comment']['ui_name'] = 'comments and posts';
$handler->display->display_options['relationships']['uid_comment']['label'] = 'comments and posts';
/* Relationship: comments only */
$handler->display->display_options['relationships']['uid_comment_1']['id'] = 'uid_comment_1';
$handler->display->display_options['relationships']['uid_comment_1']['table'] = 'users';
$handler->display->display_options['relationships']['uid_comment_1']['field'] = 'uid_comment';
$handler->display->display_options['relationships']['uid_comment_1']['ui_name'] = 'comments only';
/* Relationship: comments_nid */
$handler->display->display_options['relationships']['nid']['id'] = 'nid';
$handler->display->display_options['relationships']['nid']['table'] = 'comment';
$handler->display->display_options['relationships']['nid']['field'] = 'nid';
$handler->display->display_options['relationships']['nid']['relationship'] = 'uid_comment_1';
$handler->display->display_options['relationships']['nid']['ui_name'] = 'comments_nid';
$handler->display->display_options['relationships']['nid']['label'] = 'comments_nid';
/* Relationship: posts only */
$handler->display->display_options['relationships']['uid_comment_2']['id'] = 'uid_comment_2';
$handler->display->display_options['relationships']['uid_comment_2']['table'] = 'users';
$handler->display->display_options['relationships']['uid_comment_2']['field'] = 'uid_comment';
$handler->display->display_options['relationships']['uid_comment_2']['ui_name'] = 'posts only';
$handler->display->display_options['relationships']['uid_comment_2']['label'] = 'posts';
/* Relationship: posts_nid */
$handler->display->display_options['relationships']['nid_1']['id'] = 'nid_1';
$handler->display->display_options['relationships']['nid_1']['table'] = 'comment';
$handler->display->display_options['relationships']['nid_1']['field'] = 'nid';
$handler->display->display_options['relationships']['nid_1']['relationship'] = 'uid_comment_2';
$handler->display->display_options['relationships']['nid_1']['ui_name'] = 'posts_nid';
$handler->display->display_options['relationships']['nid_1']['label'] = 'posts_nid';
/* Champ: Utilisateur: Image */
$handler->display->display_options['fields']['picture']['id'] = 'picture';
$handler->display->display_options['fields']['picture']['table'] = 'users';
$handler->display->display_options['fields']['picture']['field'] = 'picture';
$handler->display->display_options['fields']['picture']['label'] = '';
$handler->display->display_options['fields']['picture']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['picture']['image_style'] = 'thumbnail';
/* Champ: Utilisateur: Nom */
$handler->display->display_options['fields']['name']['id'] = 'name';
$handler->display->display_options['fields']['name']['table'] = 'users';
$handler->display->display_options['fields']['name']['field'] = 'name';
$handler->display->display_options['fields']['name']['label'] = '';
$handler->display->display_options['fields']['name']['alter']['word_boundary'] = FALSE;
$handler->display->display_options['fields']['name']['alter']['ellipsis'] = FALSE;
/* Champ: COUNT(DISTINCT Commentaire: Identifiant (ID)) */
$handler->display->display_options['fields']['cid']['id'] = 'cid';
$handler->display->display_options['fields']['cid']['table'] = 'comment';
$handler->display->display_options['fields']['cid']['field'] = 'cid';
$handler->display->display_options['fields']['cid']['relationship'] = 'uid_comment';
$handler->display->display_options['fields']['cid']['group_type'] = 'count_distinct';
$handler->display->display_options['fields']['cid']['label'] = '';
$handler->display->display_options['fields']['cid']['exclude'] = TRUE;
$handler->display->display_options['fields']['cid']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['cid']['prefix'] = 'Total (commentaires et posts) :';
/* Champ: COUNT(DISTINCT Commentaire: Identifiant (ID)) */
$handler->display->display_options['fields']['cid_2']['id'] = 'cid_2';
$handler->display->display_options['fields']['cid_2']['table'] = 'comment';
$handler->display->display_options['fields']['cid_2']['field'] = 'cid';
$handler->display->display_options['fields']['cid_2']['relationship'] = 'uid_comment_2';
$handler->display->display_options['fields']['cid_2']['group_type'] = 'count_distinct';
$handler->display->display_options['fields']['cid_2']['label'] = '';
$handler->display->display_options['fields']['cid_2']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['cid_2']['suffix'] = ' posts sur le forum';
/* Champ: COUNT(DISTINCT Commentaire: Identifiant (ID)) */
$handler->display->display_options['fields']['cid_1']['id'] = 'cid_1';
$handler->display->display_options['fields']['cid_1']['table'] = 'comment';
$handler->display->display_options['fields']['cid_1']['field'] = 'cid';
$handler->display->display_options['fields']['cid_1']['relationship'] = 'uid_comment_1';
$handler->display->display_options['fields']['cid_1']['group_type'] = 'count_distinct';
$handler->display->display_options['fields']['cid_1']['label'] = '';
$handler->display->display_options['fields']['cid_1']['element_label_colon'] = FALSE;
$handler->display->display_options['fields']['cid_1']['suffix'] = ' avis postés';
/* Sort criterion: Utilisateur: Date de création */
$handler->display->display_options['sorts']['created']['id'] = 'created';
$handler->display->display_options['sorts']['created']['table'] = 'users';
$handler->display->display_options['sorts']['created']['field'] = 'created';
$handler->display->display_options['sorts']['created']['order'] = 'DESC';
/* Filter criterion: Utilisateur: Actif */
$handler->display->display_options['filters']['status']['id'] = 'status';
$handler->display->display_options['filters']['status']['table'] = 'users';
$handler->display->display_options['filters']['status']['field'] = 'status';
$handler->display->display_options['filters']['status']['value'] = '1';
$handler->display->display_options['filters']['status']['group'] = 1;
$handler->display->display_options['filters']['status']['expose']['operator'] = FALSE;
/* Filter criterion: Utilisateur: Rôles */
$handler->display->display_options['filters']['rid']['id'] = 'rid';
$handler->display->display_options['filters']['rid']['table'] = 'users_roles';
$handler->display->display_options['filters']['rid']['field'] = 'rid';
$handler->display->display_options['filters']['rid']['operator'] = 'not';
$handler->display->display_options['filters']['rid']['value'] = array(
3 => '3',
);
/* Filter criterion: Contenu: Type */
$handler->display->display_options['filters']['type']['id'] = 'type';
$handler->display->display_options['filters']['type']['table'] = 'node';
$handler->display->display_options['filters']['type']['field'] = 'type';
$handler->display->display_options['filters']['type']['relationship'] = 'nid';
$handler->display->display_options['filters']['type']['operator'] = 'not in';
$handler->display->display_options['filters']['type']['value'] = array(
'forum' => 'forum',
);
/* Filter criterion: Contenu: Type */
$handler->display->display_options['filters']['type_1']['id'] = 'type_1';
$handler->display->display_options['filters']['type_1']['table'] = 'node';
$handler->display->display_options['filters']['type_1']['field'] = 'type';
$handler->display->display_options['filters']['type_1']['relationship'] = 'nid_1';
$handler->display->display_options['filters']['type_1']['value'] = array(
'forum' => 'forum',
);
/* Display: 10 derniers membres */
$handler = $view->new_display('block', '10 derniers membres', 'last_members');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['title'] = 'Les derniers inscrits';
$handler->display->display_options['display_description'] = 'les 10 derniers utilisateurs a s\'être inscrit sur le site';
$handler->display->display_options['defaults']['use_more'] = FALSE;
$handler->display->display_options['defaults']['use_more_always'] = FALSE;
$handler->display->display_options['defaults']['use_more_always'] = FALSE;
$handler->display->display_options['use_more_always'] = TRUE;
$handler->display->display_options['defaults']['use_more_text'] = FALSE;
$handler->display->display_options['use_more_text'] = 'voir tout les membres';
$handler->display->display_options['block_description'] = '10 derniers membres';
/* Display: 10 membres les plus actifs */
$handler = $view->new_display('block', '10 membres les plus actifs', 'active_members');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['title'] = 'Les plus actifs';
$handler->display->display_options['display_description'] = 'les 10 utilisateurs qui ont poster le plus de commentaire ou de post sur le forum';
$handler->display->display_options['defaults']['sorts'] = FALSE;
/* Sort criterion: COUNT(DISTINCT Commentaire: Identifiant (ID)) */
$handler->display->display_options['sorts']['cid']['id'] = 'cid';
$handler->display->display_options['sorts']['cid']['table'] = 'comment';
$handler->display->display_options['sorts']['cid']['field'] = 'cid';
$handler->display->display_options['sorts']['cid']['relationship'] = 'uid_comment';
$handler->display->display_options['sorts']['cid']['group_type'] = 'count_distinct';
$handler->display->display_options['sorts']['cid']['order'] = 'DESC';
$handler->display->display_options['block_description'] = '10 membres les plus actifs';
/* Display: tous les membres */
$handler = $view->new_display('block', 'tous les membres', 'all_members');
$handler->display->display_options['defaults']['title'] = FALSE;
$handler->display->display_options['title'] = 'Tous les membres du club BBchoo';
$handler->display->display_options['display_description'] = 'tous les membres par ordre alphabetique';
$handler->display->display_options['defaults']['pager'] = FALSE;
$handler->display->display_options['pager']['type'] = 'full';
$handler->display->display_options['pager']['options']['items_per_page'] = '10';
$handler->display->display_options['pager']['options']['offset'] = '0';
$handler->display->display_options['pager']['options']['id'] = '0';
$handler->display->display_options['pager']['options']['quantity'] = '9';
$handler->display->display_options['pager']['options']['expose']['items_per_page_label'] = 'Éléments par page';
$handler->display->display_options['pager']['options']['expose']['items_per_page_options_all_label'] = '- Tout -';
$handler->display->display_options['pager']['options']['expose']['offset_label'] = 'Décalage';
$handler->display->display_options['pager']['options']['tags']['first'] = '« premier';
$handler->display->display_options['pager']['options']['tags']['previous'] = '‹ précédent';
$handler->display->display_options['pager']['options']['tags']['next'] = 'suivant ›';
$handler->display->display_options['pager']['options']['tags']['last'] = 'dernier »';
$handler->display->display_options['defaults']['sorts'] = FALSE;
/* Sort criterion: Utilisateur: Nom */
$handler->display->display_options['sorts']['name']['id'] = 'name';
$handler->display->display_options['sorts']['name']['table'] = 'users';
$handler->display->display_options['sorts']['name']['field'] = 'name';
$handler->display->display_options['block_description'] = 'tous les membres';
$translatables['utilisateurs'] = array(
t('Master'),
t('utilisateurs'),
t('plus'),
t('Appliquer'),
t('Réinitialiser'),
t('Trier par'),
t('Asc'),
t('Desc'),
t('comments and posts'),
t('comments'),
t('comments_nid'),
t('posts'),
t('posts_nid'),
t('10 derniers membres'),
t('Les derniers inscrits'),
t('les 10 derniers utilisateurs a s\'être inscrit sur le site'),
t('voir tout les membres'),
t('10 membres les plus actifs'),
t('Les plus actifs'),
t('les 10 utilisateurs qui ont poster le plus de commentaire ou de post sur le forum'),
t('tous les membres'),
t('Tous les membres du club BBchoo'),
t('tous les membres par ordre alphabetique'),
t('Éléments par page'),
t('- Tout -'),
t('Décalage'),
t('« premier'),
t('‹ précédent'),
t('suivant ›'),
t('dernier »'),
);
?>
The problem is it returns only users who have ever posted comments and forum messages, not users who posted nothing, not users who posted only comments and not users who posted only forum messages.
How can I configure my view display to fix it ?
