Index: openacs-4/packages/contacts/lib/contact-relationships-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/contacts/lib/contact-relationships-postgresql.xql,v
diff -u -r1.9 -r1.10
--- openacs-4/packages/contacts/lib/contact-relationships-postgresql.xql	4 May 2006 12:01:47 -0000	1.9
+++ openacs-4/packages/contacts/lib/contact-relationships-postgresql.xql	18 May 2006 18:02:59 -0000	1.10
@@ -4,21 +4,39 @@
 <fullquery name="get_relationships">
       <querytext>
 select rel_id, other_name, other_party_id, role_singular, rel_type, creation_date
-  from ( select CASE WHEN object_id_one = :party_id THEN contact__name(object_id_two,'t') ELSE contact__name(object_id_one,'t') END as other_name,
-		CASE WHEN object_id_one = :party_id THEN object_id_two ELSE object_id_one END as other_party_id,
-                CASE WHEN object_id_one = :party_id THEN role_two ELSE role_one END as role,
-                CASE WHEN object_id_one = :party_id THEN acs_rel_type__role_pretty_name(role_two) ELSE acs_rel_type__role_pretty_name(role_one) END as role_singular,
+  from ( select contact__name(object_id_two,'t') as other_name,
+		object_id_two as other_party_id,
+                role_two as role,
+                pretty_name as role_singular,
                 acs_rels.rel_id, acs_rels.rel_type
-           from acs_rels,
-                acs_rel_types
+           from acs_rels, 
+                acs_rel_types,
+		acs_rel_roles,
+		group_distinct_member_map
           where acs_rels.rel_type = acs_rel_types.rel_type
-	    and acs_objects.object_id = acs_rels.rel_id
-            and ( object_id_one = :party_id or object_id_two = :party_id )
+            and object_id_one = :party_id
+	    and acs_rel_types.role_two = acs_rel_roles.role
+	    and object_id_two = group_distinct_member_map.member_id
+	    and group_distinct_member_map.group_id in ('-2')
             and acs_rels.rel_type in ( select object_type from acs_object_types where supertype = 'contact_rel')
-       ) rels_temp, acs_objects, group_distinct_member_map
-	where rels_temp.rel_id = acs_objects.object_id
-          and rels_temp.other_party_id = group_distinct_member_map.member_id
-          and group_distinct_member_map.group_id in ([template::util::tcl_to_sql_list [contacts::default_groups]])
+	 union 
+	 select contact__name(object_id_one,'t') as other_name,
+		object_id_one as other_party_id,
+                role_one as role,
+                pretty_name as role_singular,
+                acs_rels.rel_id, acs_rels.rel_type
+           from acs_rels, 
+                acs_rel_types,
+		acs_rel_roles,
+		group_distinct_member_map
+          where acs_rels.rel_type = acs_rel_types.rel_type
+            and object_id_two = :party_id
+	    and acs_rel_types.role_one = acs_rel_roles.role
+	    and object_id_one = group_distinct_member_map.member_id
+	    and group_distinct_member_map.group_id in ('-2')
+            and acs_rels.rel_type in ( select object_type from acs_object_types where supertype = 'contact_rel')
+       ) rels_temp, acs_objects
+where rels_temp.rel_id = acs_objects.object_id
  order by upper(role_singular) asc, $sort_order
       </querytext>
 </fullquery>