Drupal7: Views Duplicate Content Audit Page

David G - DrupalIn a recent project I’m doing maintenance work on, I provide access to a Webform via a secret URL key. When I developed this site I did not make use of a ticketing system to assure that all these keys are unique. Now that I’m revamping the system to add some monitoring and reporting tools for myself to oversee the application remotely. I’ve finally figured out in a few minutes how to display a duplicate report listing using Views …

When I created this project originally this audit page I wanted to alert myself of duplicate secret keys (a No No in this system — every webform should be given 1 unique secret key) … but I couldn’t at the time, under pressure, figure out the View for this. In SQL I know I’d be using a GROUP clause typically along with a COUNT expression. Lately, I’ve been using Views alot more, and Aggregation, so after doing other projects and having the ideas percolate in my brain the solution simply became apparent today. This is the final result for my audit page:

Audit page for Admin user.

Audit page for Admin user.

This page is basically a View for me. The client basically doesn’t need to know about this page. But, at least I have a report page I can goto to check for duplicate entries. And on this page since I list the individual content items using these keys, I can easily search on the page for the offending duplicate key instance(s) press Edit and manually fix the issue.

In running this site for 2 quarters now and processing onwards of 1000+ applications I’ve not seen duplicate keys ever (I use like 5 pieces of information to create a hash value) … but I know it’s a possibility. Hence this page.

So what is this page you may ask? It’s all simply a single View Page configuration with an Attached view:

Master View Page creates the duplicate key report using Aggregation settings.

Master View Page creates the duplicate key report using Aggregation settings.

Attachment View display. This places the general key list below the duplicate report.

Attachment View display. This places the general key list below the duplicate report.

The most important thing is the aggregation settings on the Master (page) view. By using Aggregation we can see the SQL produced by the system Counts uniqueness of authkey for the report in the HAVING clause below:

SELECT field_data_field_rel_authkey.field_rel_authkey_value AS field_data_field_rel_authkey_field_rel_authkey_value, MIN(relation.rid) AS rid, 'relation' AS field_data_field_rel_authkey_relation_entity_type, COUNT(field_data_field_rel_authkey.field_rel_authkey_value) AS field_data_field_rel_authkey_field_rel_authkey_value_1, COUNT(field_data_field_rel_authkey.delta) AS field_data_field_rel_authkey_delta, COUNT(field_data_field_rel_authkey.language) AS field_data_field_rel_authkey_language, COUNT(field_data_field_rel_authkey.bundle) AS field_data_field_rel_authkey_bundle, COUNT(field_data_field_rel_authkey.field_rel_authkey_format) AS field_data_field_rel_authkey_field_rel_authkey_format
{relation} relation
LEFT JOIN {field_data_field_rel_authkey} field_data_field_rel_authkey ON relation.rid = field_data_field_rel_authkey.entity_id AND (field_data_field_rel_authkey.entity_type = 'relation' AND field_data_field_rel_authkey.deleted = '0')
WHERE (( (relation.relation_type IN  ('summer_session_user_has_survey')) ))
GROUP BY field_data_field_rel_authkey_relation_entity_type, field_data_field_rel_authkey_field_rel_authkey_value
HAVING (( (COUNT(field_data_field_rel_authkey.field_rel_authkey_value) > '1') ))
ORDER BY field_data_field_rel_authkey_field_rel_authkey_value_1 DESC

This is what we want (albeit a little ugly). When using aggregation it’s important to really narrow down the unique fields you want to compare in your report. If I included the NID or RID of the items in this case into my Fields list duplicates would never be found. By definition every resultset rows’ primary key value would always be unique!

The other caveat on this page that I can’t really show you is — well; the Rules sortable table headers affect both tables simultaneously when clicked!!!?!? This is a HUGE bummer and outlier issues like this really annoy me when developing. This is a known, longstanding issue with Views [see Views Issue Queue: Implementing table sort for multiple tables on single page] opened in 2011.

But I have my report now. In other blog posts I’ve mentioned the Views module and Migrate. An example addition I want to add to this report/functionality is add a Migration task to use Rules to email a summary of the duplicate report when a Migration is performed on the system. As of Febuary 2015 I’ve done all these things once before! I just need more time in a day and another pair of hands to finish the work 😛

Looking for quality web hosting? Look no further than Arvixe Web Hosting!

Tags: , , , | Posted under Drupal | RSS 2.0

Author Spotlight

David Gurba

I am a web programmer currently employed at UCSB. I have been developing web applications professionally for 8+ years now. For the last 5 years I’ve been actively developing websites primarily in PHP using Drupal. I have experience using LAMP and developing data driven websites for clients in aviation, higher education and e-commerce. If you’d like to contact me I can be reached at david.gurba@arvixe.com

Leave a Reply

Your email address will not be published. Required fields are marked *