Views and Relationship Puzzle

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
camhoward's picture

I am struggling with Views and Relationships and after many hours (days) of reading articles, watching videos, and experimenting with the settings, I'm stuck. Are there any Views experts in the group who can point me in the right direction?

Goal: Create a View with a Page display to display each author's name, photo, and the taxonomy terms on the articles they authored. (Note: Authors are not Users on this site; the author writes the article, someone else creates the node.)

Another way to say it is: Display taxonomy terms from Articles when Article Author matches Author's Profile name.

Content types: Profile (author's name, photo, and bio) and Article. The Article content type includes a Term Reference field enabling multiple taxonomy terms to be selected, and a Node Reference field to link to the author's Profile.

The View for the Page display uses the following:

  1. Format: Unformatted list
  2. Show: Fields
  3. Fields: Title (author's name), Image, Tags (the Term Reference field in the Article content type; in the View it uses Relationship: field_author - reverse)
  4. Filter Criteria: Published (Yes), Type (= Profile), Has taxonomy term (= Author)
  5. Relationship: field_author - reverse (this is the Node Reference field in the Article content type that links to the author's Profile)

Result: The View's Page Display shows a list of the author's name, photo, and taxonomy terms associated with articles they have authored (so far, so good).

Problem: A separate entry for an author's name, photo, and taxonomy terms is added for every article associated with an author, so an author with 5 articles appears on the list 5 times. Each author should only be listed once, followed by a compilation or aggregation of all the taxonomy terms on all their articles.

Attempted Fixes: I've tried using Distinct and Pure Distinct in the Query settings and I've tried setting Use Aggregation to yes, but neither one solves the duplication problem.

Question: How can I accomplish the goal without the duplicate entries? I'm using Drupal 7.26 and Views 7.x-3.7.

Any help is greatly appreciated! Thanks!

Comments

Likewise

BruceDawson's picture

I likewise would like a better definition of how views works. Or at least a description of how the queries are constructed.

It seems we have to make a bazillion mistakes before we start to get an idea, only to run into a scenario where we have to make more experiments to figure out our prior assumption wasn't just quite right.

Did I say views can be very frustrating?

Goto:

jnicola's picture

Goto: /admin/structure/views/settings

Enable displaying SQL Query.

This is what views is actually doing.

Relationships are a join, done outside of the standard entities joins (bringing fields in, etc). Fields added with that relationship are fields from that table. Filters are WHERE statements from that join.

So if I read correctly, you'd want to do a view of content. Filter said content to type of PROFILE. Then add a relationship for entity referenced (presuming that's how you link authors to articles). From there, add a field of taxonomy terms from the entity referenced.

Multiple entities referenced will result in multiple rows as your seeing. Views starts to fall apart right about here, which I've seen countless times.

You will probably need to do a custom hook_views_query_alter() to do a GROUP BY on the profile NID... but I am not certain as this all gets to be a bit much for me to think out hypothetically.

What I personally would do, is get your hook in place with a custom module, var_dump(); the $query and exit;... or DPM($query) if you'd prefer and have devel module enabled. You should be able to alter the results to get what you are looking for.

As far as I am aware though, there is no views recipe out of the box to do this. Views tends to fall apart along complex relationships like this, that incidentally are far easier and less complicated queries to write by yourself :)

Jesse Nicola -- Shredical six different ways to Sunday! -- My Portfolio

The solution is about data

Bob Newby's picture

The solution is about data modeling and representation. The solution is not about Views per se.

See my comment to the top-level thread.

Thanks!

camhoward's picture

@BruceDawson -- Yes, Views can be very frustrating! Really useful, but can sometimes take a long time to figure out.

@jnicola -- Thanks for the suggestion to turn on display of the SQL Query. Nice to see what's going on behind the View!

Your description of how to set up the View is just what I've done, so I appreciate the confirmation that the View itself is set up correctly. It's also helpful to learn that there is no way to accomplish what I'm trying to do in Views itself.

After reading your thought about using GROUP BY, I tried using Content: Title as the Grouping Field in the Format Settings, but it didn't eliminate the duplicates.

I'll see if I can learn how to write a custom module for this.

I appreciate the feedback! At least now I know not to keep banging my head against the wall trying to do it in Views.

Thanks!

It's a pretty quick thing to

jnicola's picture

It's a pretty quick thing to do, building a custom module for this. It's not as intimidating as you might think. Create the .info file, then the .module file, and create the function my_module_hook_views_query_alter() and you're in business. One of the great things about Drupal :)

Jesse Nicola -- Shredical six different ways to Sunday! -- My Portfolio

There is a Views-based solution

Bob Newby's picture

Hi Cam,

Re: Problem: A separate entry for an author's name, photo, and taxonomy terms is added for every article associated with an author, so an author with 5 articles appears on the list 5 times. Each author should only be listed once, followed by a compilation or aggregation of all the taxonomy terms on all their articles.

There is at least one Views-based solution, but I may be challenged to adequately describe it in writing.

Nonetheless here is a partial sketch. It assumes that the relationship between a Profile and an Article is 1-to-many, and that this is captured via Entity Reference instances. Also, the solution requires use of the Entity Reference View Widget module.

A. Create a view of all the Articles associated with a particular Author/Profile. Let's call this view A. A displays, say, a table where each column contains the fields you are interested in. Of course each row corresponds to a single Article.

B. In the Profile type that is used to represent an Author, add a Views Field -- I am not off-hand sure of the type I use, but I can look this up -- and then configure this field to use the Entity Reference View Widget. Then point the field/widget to View A, above.

C. I suggest now setting up a Profile display for an appropriately named view mode (this is a Display Suite thing, not a Views thing), say something named "Author and Articles". Configure this display to contain the Profile data you want to display. It should also contain the field discussed above in B, keeping in mind that this field is tied to view A, above.

D. Now create a Profile/Author view. Let's call it view D. It displays one or more Profile/Author nodes, where each node is rendered as the Profile display discussed above in C, i.e., the one named "Author and Articles".

Yes, that's basically it.

I will be happy to Skype with you to walk through this using screen sharing (my Skype id is bob_newby). I use the above tactic from time-to-time on my LuthierBuilt.net site.

Hope this helps,

Bob

New Hampshire

Group categories

Regional Audience

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds: