By colin_e on

We're using Drupal 5.x and Views 1

I want to create a view that will recommend "groups you have NOT joined yet" to a user, but i'm having trouble getting it working.

A typical View filter for "my groups" would be-

OG: Group in User Subbed Groups Is Equal to Currently logged in user

Unfortunately, just changing the filter function in the middle-

OG: Group in User Subbed Groups Is Not Equal to Currently logged in user

doesn't work. Not only do groups the user IS a member of appear in the list, but the same group can also occur multiple times! I think I am actually getting something like "List all groups whose members include someone other than me", that is not what I need.

Is this apparently simple search possible in Views, or do I need a custom coded view? (probabaly beyond my limited programming skills).

⋅ Categories: Drupal 5.x

Comments

colin_e’s picture

colin_e commented

Oook, in the spirit of trying to increase the answer-to-question ratio a bit here, after a lot of late night Googling and going down blind alleys with Views arguments
etc., I have come to the following conclusion on two questions-

  1. Is it possible to build a view of groups the user is NOT currently a member
    of? No- you need a custom SQL query.
  2. Is it possible to build a view ontop of a custom SQL query? Ntt exactly,
    but you CAN build a "view-like table" that looks pretty much the same to
    the end user.

The following code, dropped into a page or block with PHP execution enabled, builds a "Promo groups" list for the current user based on- 1) The group having the"promoted" flag set, 2) The user not already being a member of the group.

Note:

* Our database schema has custom elements, the query will need to be changed to suit your database structure.

  /*
   * Display a set of groups recommended to the user based on the fact that they
   * have the "promote" flag set, and the user is NOT already a member.
   * To do this we have to construct a "view-like" display by hand, as Views
   * can't build the query we need.
   * 
   * Note: Drupal 5.x, Views 1
   * colin_e 19/05/2009   
   */

  global $user;
  
  $sql= sprintf("SELECT n.nid, n.title, n.created, g.field_community_description_value AS description"
               ." FROM node n JOIN content_type_group g ON n.nid=g.nid"
			   ." WHERE n.nid IN"
			   ." ( SELECT DISTINCT nid FROM og_uid WHERE status=1 AND promote=1 AND nid NOT IN"
			   ." (SELECT nid from og_uid where uid=%d) )"
			   , $user->uid);

  /*
   * This far from obvious procedure is based on the technique
   * described at- http://drupal.org/node/280069.
   */
  $header= array(
    array('data' => 'Community', 'field' => 'title')
  , array('data' => 'Description', 'field' => 'description')
  , array('data' => 'Created', 'field' => 'created')
  );

  $sql .= tablesort_sql($header, $before= 'sticky desc,');

  $result= db_query($sql);

  if(!db_num_rows($result)) exit;       // No display if no result

  $body= array();
  while ($row = db_fetch_object($result)) {
    $body[]= array(
        l($row->title, 'node/'.$row->nid, array())
	  , $row->description
      , format_date($row->created, 'custom', 'j M Y')
    );
  }


<div id="block-og_furniture-promo_groups" class="clear-block block block-og_furniture">
  <h2><span>Promo Groups</span></h2>                
  <div class="content">                 
    <div class='view view-promo-groups'>                     
      <div class='view-content view-content-promo-groups'>                            
        <?php echo theme('table', $header, $body); ?>            
      </div>                 
    </div>             
  </div>         
</div>