1

I want to create a table displaying counts of users' authored nodes and revisions (we're doing some "wiki" type stuff). This is for a "top contributors" type display.

The table should be structured with three columns: Name, Posts, Revisions. The table should list users under "Name" and numbers under "Posts" and "Revisions".

I feel like I got halfway there and got stumped. Here's what I set up in a view:

(NOTE: Users are affiliated with organizations via References module and "Organization" content type, I'm trying to display users affiliated with the org on the org page.)

Fields:
(author) User: Name (Staff)
COUNT(Content: Title) (Title)
(revision user) User: Name (Name)
COUNT(Content revision: Vid) (Vid)

Filter Criteria:
Content: Type (= Resource)

Sort Criteria: NONE

Contextual Filters:
(author) User: Organization - Provide default value: Content ID from URL

Relationships:
Content: Author
(author) User: Organization
Content revision: User
(revision user) User: Organization

Other:
Use Aggregation: Yes

The result I get shows the same number of posts as revisions - even when I go throw in a few additional revisions to test.

Where am I going wrong? How do I do this properly?

ADDED TO QUESTION

Here's the full view export:

$view = new view();
$view->name = 'staff_contributions';
$view->description = '';
$view->tag = 'default';
$view->base_table = 'node';
$view->human_name = 'Staff Contributions';
$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'] = 'Staff Contributions';
$handler->display->display_options['use_more_always'] = FALSE;
$handler->display->display_options['group_by'] = TRUE;
$handler->display->display_options['access']['type'] = 'perm';
$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['pager']['type'] = 'some';
$handler->display->display_options['pager']['options']['items_per_page'] = '5';
$handler->display->display_options['style_plugin'] = 'default';
$handler->display->display_options['row_plugin'] = 'fields';
/* Relationship: Content: Author */
$handler->display->display_options['relationships']['uid']['id'] = 'uid';
$handler->display->display_options['relationships']['uid']['table'] = 'node';
$handler->display->display_options['relationships']['uid']['field'] = 'uid';
/* Relationship: User: Organization (field_organization) */
$handler->display->display_options['relationships']['field_organization_nid']['id'] = 'field_organization_nid';
$handler->display->display_options['relationships']['field_organization_nid']['table'] = 'field_data_field_organization';
$handler->display->display_options['relationships']['field_organization_nid']['field'] = 'field_organization_nid';
$handler->display->display_options['relationships']['field_organization_nid']['relationship'] = 'uid';
$handler->display->display_options['relationships']['field_organization_nid']['delta'] = '-1';
/* Relationship: Content revision: User */
$handler->display->display_options['relationships']['uid_1']['id'] = 'uid_1';
$handler->display->display_options['relationships']['uid_1']['table'] = 'node_revision';
$handler->display->display_options['relationships']['uid_1']['field'] = 'uid';
/* Relationship: User: Organization (field_organization) */
$handler->display->display_options['relationships']['field_organization_nid_1']['id'] = 'field_organization_nid_1';
$handler->display->display_options['relationships']['field_organization_nid_1']['table'] = 'field_data_field_organization';
$handler->display->display_options['relationships']['field_organization_nid_1']['field'] = 'field_organization_nid';
$handler->display->display_options['relationships']['field_organization_nid_1']['relationship'] = 'uid_1';
$handler->display->display_options['relationships']['field_organization_nid_1']['delta'] = '-1';
/* Field: User: Name */
$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']['relationship'] = 'uid';
$handler->display->display_options['fields']['name']['label'] = 'Staff';
/* Field: COUNT(DISTINCT Content: Title) */
$handler->display->display_options['fields']['title']['id'] = 'title';
$handler->display->display_options['fields']['title']['table'] = 'node';
$handler->display->display_options['fields']['title']['field'] = 'title';
$handler->display->display_options['fields']['title']['group_type'] = 'count_distinct'; /* Field: User: Name */
$handler->display->display_options['fields']['name_1']['id'] = 'name_1';
$handler->display->display_options['fields']['name_1']['table'] = 'users';
$handler->display->display_options['fields']['name_1']['field'] = 'name';
$handler->display->display_options['fields']['name_1']['relationship'] = 'uid_1';
/* Field: COUNT(DISTINCT Content revision: Vid) */
$handler->display->display_options['fields']['vid']['id'] = 'vid';
$handler->display->display_options['fields']['vid']['table'] = 'node_revision';
$handler->display->display_options['fields']['vid']['field'] = 'vid';
$handler->display->display_options['fields']['vid']['group_type'] = 'count_distinct';
/* Contextual filter: User: Organization (field_organization) */
$handler->display->display_options['arguments']['field_organization_nid']['id'] = 'field_organization_nid';
$handler->display->display_options['arguments']['field_organization_nid']['table'] = 'field_data_field_organization';
$handler->display->display_options['arguments']['field_organization_nid']['field'] = 'field_organization_nid';
$handler->display->display_options['arguments']['field_organization_nid']['relationship'] = 'uid';
$handler->display->display_options['arguments']['field_organization_nid']['default_action'] = 'default';
$handler->display->display_options['arguments']['field_organization_nid']['default_argument_type'] = 'node';
$handler->display->display_options['arguments']['field_organization_nid']['summary']['number_of_records'] = '0';
$handler->display->display_options['arguments']['field_organization_nid']['summary']['format'] = 'default_summary';
$handler->display->display_options['arguments']['field_organization_nid']['summary_options']['items_per_page'] = '25';
/* Filter criterion: Content: 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']['value'] = array( 'resource' => 'resource', );

/* Display: Block */
$handler = $view->new_display('block', 'Block', 'block');
$handler->display->display_options['defaults']['hide_admin_links'] = FALSE;

Improve this question

2 Answers 2

Reset to default
0

I'm assuming that your view is set up to display content revisions (you have to select what it's displaying on the first screen when setting up a view).

Try selecting '*Count DISTINC*T' in the aggregation settings! That should do it. Otherwise, even if your view is outputting something like

user: user1
nid: 510
vid: 1

user: user1
nid: 510
vid: 2

user: user1
nid: 510
vid: 3

It would be counting those nids at '3' rather than '1' if you don't count the DISTINCT instances. Does that make sense?

Hope this gets you in the right direction...

============================ RESPONSE ADDITION ==============

Just wanted to add that if you can't get quite the right view with what the View's UI provides (because it's not quite as flexible as writing your own SQL), you can always choose to write a tiny custom module that overrides the SQL of your view.

Something like this: http://eureka.ykyuen.info/2010/11/27/drupal-customize-the-views-query/

I just wanted to add that in case it helps too. :-)

Improve this answer
3
  • Thanks Boriana. Tried changing COUNT to COUNT DISTINCT on content title and VID. Didn't affect anything. I'll try messing a little more with the relationship structure.
    – Phil Owen
    Commented Oct 11, 2012 at 1:30
  • You know, I think it's possible to get the number of nodes authored per person and the number of revisions made, but I can only get them in two distinct views. The relationship structure just isn't flexible enough to combine all of that in one view, I think. I'd consider trying out tinkering with the SQL of your view as I added to my response if you want to make sure all that info is together. Or you could have it displayed in separate blocks in two separate views!
    – Boriana Ditcheva
    Commented Oct 11, 2012 at 13:06
  • Ok, I'll give it a try. My PHP/SQL skills are pretty shoddy - but hey, can't learn without diving in, right?
    – Phil Owen
    Commented Oct 12, 2012 at 20:43
1

Here's what I ended up doing - it worked like a charm:

I installed the Userpoints module and the Userpoints Nodes and Comments module.

Site users like recognition/credit, but aren't quite looking to rack up points or compete, so I just changed the "Points" branding (under configuration > points > points settings > branding) to "Contributions".

Set the system to add one point for creating one node, creating one revision, creating one comment, etc. Then set the userpoints categories (under structure > taxonomy) to "Resources"(node contributions), "Revisions", "Comments", etc.

Set default user points category for each content type (structure > content type)

User profiles now show how many resource posts, revisions, comments, etc they've respectively contributed:

enter image description here


To show top users, just create a view with the User:Name field and Userpoints:Total Current Points field, and adjust the points field title to "Site Contributions" (near bottom of pic):

enter image description here

Using relationships and aggregation I was able to create a "Top Organizations" view as well:

enter image description here

@Boriana - I'll still try the SQL approach. Need to learn it.

Thanks!

Improve this answer
1
  • That's a very creative solution! Thanks for sharing, Phil.
    – Boriana Ditcheva
    Commented Oct 13, 2012 at 2:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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