Filtering Views using referencing nodes

Question

I have a bit more complicated problem here, so I'll describe the background first.

In my Drupal 7 site, there are two content types: the 'car' and the 'inspection'. The only two important things about them is that the 'inspection' includes 'Date' field and that 'car' and 'inspection' are linked using Node References module (there's Node Reference Field named 'Car' in 'Inspection').

Now, here's my problem. I need to create a view that shows only the cars that haven't been on inspection in last two years which means that they have no 'inspection' with date > two years ago referencing them.

I'm able to achieve that if there's only one or none inspection referencing the car. In that case, I simply use 'Filter Criteria' on Date property of Nodes referencing this car (using relationships) and it works just fine.

However, if there are more 'inspections', the filter takes every single one and list the car whenever there's at least one with date < 2 years ago (even if there's another one newer).

What I need is to somehow create a filter that would consider all 'inspection' nodes that are referencing the 'car' and shows the car only if there was none that would be newer than 2 years ago.

I hope it is clear.

//I have one more smaller problem that is (probably) connected with this one. I need to show the date of the newest inspection of a car in a view of cars. The view, however, always shows the oldest one (Table->Fields-> Date field of 'inspection'-> Nodes referencing this one 'car').

//BTW: If you don't understand the problem I have because of my level of English, please tell me. I'll try to rewrite it in a simpler way.

Answer 1

The first thing I would say is that it sounds as if you have node references going both ways. This is generally a bad thing in terms of database structures. In this case you should only have inspections pointing at cars and use reverse references if you need to do the opposite.

Anyway here's what I would do:

A view based on inspections, filter the content type to inspections only and also filter the date. That gives you all the old inspections.

Put the Car Node Ref into the field list and then Group By that field (Group By is under View > Advanced > Use aggregation)

You could then add a relationship based on the Car Node Ref which then gives you access to the individual Car fields.

This will give you one row per car for inspections older than [whatever you choose].

My only concern then is whether you might get a car which has inspections for >2 years ago and also more recent ones. So will appear in the list even though it has more recent ones.