Drupal Views - Showing distinct results

To clarify what we have in our scenario, we have several "Artists" for example: Picasso and Van Gogh. Both Artists have several "Paintings" which have a CCK Node Reference field that both reference the Artist name.

We also are using a Views Exposed Filter and when using this, we can search for paintings with a specific value. The value is a field belonging to the "Painting" - therefore when we search, we are delivered duplicated results showing paintings from both of our Artists. Ideally, we should only be showing their names once as each painting is already shown on the specific node.

This solution will show you how to only show the Artist name once, and link you to their page.

To do this, you should amend the SQL Query that is run by the View. To do this we need a custom module, and a snippet of code.

The function we will use is called:

function yourmodulename_views_pre_execute(&$view)

Our current view is called "search_artists" and the field that we need to use to make our listings unique values only, is "node_node_data_field_parent_artist".

First we need to replace "yourmodulename" with the name of your custom module, in this case I have created a custom module called "custom_overrides".

function custom_overrides_views_pre_execute(&$view){
if ($view->name == 'search_artists') {
$search = array('GROUP BY nid')
$replace = array('GROUP BY node_node_data_field_artist');
$view->build_info['query'] = str_replace($search,$view->build_info['query']); }}

Here we are simply using a string replacement on the SQL for the View, and changing "GROUP BY nid" to "GROUP BY node_node_data_field_artist" which then gives us a distinct list of our Artists in the view.