How to limit Drupal View results by relationship

Question

I have a set of Drupal magazine-related content types predefined in the following relationships:

• Story: Has a title, body, author, required node reference to an Issue, optional node reference to an Illustration.
• Article: Has a title, body, and required node reference to an Issue.
• Illustration: Has an image and a Contributor profile.
• Issue: Has a title and date.
• Contributor profile: Has a first name and last name.

What I'm trying to get is a listing of issues with an illustration from one of its stories showing, if there is one. If there aren't stories (there may be only articles), or aren't any stories with illustrations, I want to just list the issue's title.

So I'm looking for output like the following:

[StoryImage] December 2010
November 2010
[StoryImage] October 2010
[StoryImage] September 2010
etc.

I'm not concerned about styling or markup at this point--I can finagle that.

Here's the View setup I have so far that isn't working:

• Filter: Node Type = Story, Article
• Relationship:
  1. Content: Issue
  2. Content: Illustration
  3. Content: Contributor, using the Illustration relationship
• Fields:
  1. Content: Image, using the Illustration relationship
  2. Node: Title, using the Issue relationship
• Sort criteria:
  1. Issue date, descending.
  2. Contributor last name, descending.

What I get (as I'd expect) is a listing of each issue as many times as there are articles/stories in the issue. So if there are 3 items in the Dec 2010 issue and 2 in the Nov issue, here's what I get:

[StoryImage] December 2010
December 2010
December 2010
November 2010
November 2010

What I really want is the first result from each issue only. Is that possible, and if so, what am I missing?

Answer 1

In the view's advanced settings, turn on aggregation. Then make sure the first field you have in your view is the NID of the content type you're trying to get distinct results for (in your case this would be Issue I think), so that it groups by that first. You might also need to set aggregation=MIN on the Node Title field.

I found this info here: http://drupal.org/node/1200536#comment-4957410

Note: I've just noticed your question is tagged as Drupal 6, and this solution may well be specific to Drupal 7, but this answer may well help others.

Answer 2

just use the drupal db api and write a pager query. In my experience views are efficient for most cases, but not all cases.

Answer 3

Have you tried the "Distinct" filter? I am not sure if that is available for your date-field, but that is what I use whenever I get a cartesian product with views.