By arvinsingla on

I have two node types, 'Game' and 'Review'. Review has a node reference to a Game. I want to generate a list of games which currently DO NOT have reviews associated with them. It's very easy to list the games that have reviews, but I'm tearing my hair out trying to get it the other way around.

I've also given the dev version of Node Referrer a whirl using the relationships but no go. The SQL behind the query is quite simple, but no real way to get views to generate that query.

Any advice would be greatly appreciated.

⋅ Categories: Drupal 6.x

Comments

shadysamir’s picture

shadysamir commented

Did you find a way to do this? I'm stuck with the same requirement as well!

mmilo’s picture

mmilo commented

I'm trying to have this solved as well, #427072: Using NodeReferrer to show nodes that are NOT being referenced

exobuzz’s picture

exobuzz commented

I'm also having this problem

http://drupal.org/node/460272

sql created by views2

SELECT node.nid AS nid,
   node_data_field_newsimage.field_newsimage_fid AS node_data_field_newsimage_field_newsimage_fid,
   node_data_field_newsimage.field_newsimage_list AS node_data_field_newsimage_field_newsimage_list,
   node_data_field_newsimage.field_newsimage_data AS node_data_field_newsimage_field_newsimage_data,
   node.type AS node_type,
   node.vid AS node_vid,
   node.title AS node_title,
   node_revisions.teaser AS node_revisions_teaser,
   node_revisions.format AS node_revisions_format,
   node.created AS node_created,
   DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 3600 SECOND), '%Y%m%d') AS node_created_day
 FROM node node 
 LEFT JOIN content_field_organisation node_data_field_organisation ON node.vid = node_data_field_organisation.vid
 LEFT JOIN content_type_news node_data_field_newsimage ON node.vid = node_data_field_newsimage.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.type in ('news')) AND (node.status <> 0) AND (node_data_field_organisation.field_organisation_nid = 0)
   ORDER BY node_created_day DESC

sql i want

SELECT node.nid AS nid,
   node_data_field_newsimage.field_newsimage_fid AS node_data_field_newsimage_field_newsimage_fid,
   node_data_field_newsimage.field_newsimage_list AS node_data_field_newsimage_field_newsimage_list,
   node_data_field_newsimage.field_newsimage_data AS node_data_field_newsimage_field_newsimage_data,
   node.type AS node_type,
   node.vid AS node_vid,
   node.title AS node_title,
   node_revisions.teaser AS node_revisions_teaser,
   node_revisions.format AS node_revisions_format,
   node.created AS node_created,
   DATE_FORMAT((FROM_UNIXTIME(node.created) + INTERVAL 3600 SECOND), '%Y%m%d') AS node_created_day
 FROM node node 
 LEFT JOIN content_field_organisation node_data_field_organisation ON node.vid = node_data_field_organisation.vid
 LEFT JOIN content_type_news node_data_field_newsimage ON node.vid = node_data_field_newsimage.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE (node.type in ('news')) AND (node.status <> 0) AND (node_data_field_organisation.field_organisation_nid IS NULL)
   ORDER BY node_created_day DESC

(Note just the IS NULL instead of ='0')

if I could do a default argument of "IS NULL", then it would return nodes that are not referenced. I guess I should open a feature request for views?

Blueeeeie’s picture

Blueeeeie commented

Under views2, there is an option for you to add "Relationships".

Just add the nodereference field for your Review in there and check "Require this relationship".

exobuzz’s picture

exobuzz commented

That would help if i wanted nodes that had a reference. however, I want nodes which don't. see my sql above. I want to choose when the node reference IS NULL (there wasnt a match with the left join)

Blueeeeie’s picture

Blueeeeie commented

Well... I can think of a workaround if you want..

You'll need to have the Rules module installed in your site to get it to work..

Using the Game and Review example.. here's what you can do.

- Add a new CCK select list to Game called "Has review" and give it values "Yes" and "No". Then set the default value to "No".
- Then add a new triggered rule that is triggered when a new Review is added. Check if the field "Has review" in the referenced Game has been set to "Yes". If not, add the action to set the field to "Yes".
- Create a view that only retrieves the Games that has its field "Has review" set to "No"

That should be able to do...

exobuzz’s picture

exobuzz commented

I have now implemented this by hacking the view SQL (as a custom module)

<?php
// hack to change the sql for the news view
function views_views_pre_execute( &$view ) {
  if ( $view->name == 'news' and $view->args[0] == -1 ) {
    $query = $view->build_info['query'];
    $query = str_replace('field_organisation_nid = %d','field_organisation_nid IS NULL', $query);
    $view->build_info['query'] = $query;
  }
}

ugly but it works.

tpainton’s picture

tpainton commented

is this really the only way to do this?

tpainton’s picture

tpainton commented

I basically applied a rough hack too. very simply. I mearly created a triggered action that happens when I save the node that references another. The action loads the referenced node and populates a field in the referenced node that is hidden. I then use this field as a flag. Then list all nodes NOT flagged (ie field value NULL.)

macramole’s picture

macramole commented

I just add this if:

if ($view->args[0] != 'all' || $fields['field_proyecto_relacionado_nid']->content == '-1' ) :

to the fields template. If its "all" and the nid is -1 (I configured this '-1' from views) then is will show up only the ones whe doesn't have any node related

tpainton’s picture

tpainton commented

What do you mean by 'fields template'? Where does this code go?