Aller directement à la fin des métadonnées
Aller au début des métadonnées


Documentation Search


CiviCRM 4.2 Documentation

Developer Resources


CiviCRM books!

Make sure to check out our Online User/Administrator and Developer Guides! You can also support this project by ordering a hard copy.

Or support us by buying an eBook or hard copy of Using CiviCRM from Packt Publishing.

Sometimes you might want to be able to review all relationships. This query will list out all relationships between contacts.
CREATE TEMPORARY TABLE relationshipa as
 SELECT r.contact_id_a AS contact_a,r.id AS relationship_id,r.relationship_type_id AS rel_type,c.id AS contact_id,c.sort_name AS namea
 FROM civicrm_relationship AS r, civicrm_contact AS c
 WHERE  r.contact_id_a=c.id;

 
CREATE TEMPORARY TABLE relationshipb as
 SELECT s.contact_id_b AS contact_b,s.id AS relationship_id,s.relationship_type_id AS rel_type,d.id AS contact_id,d.sort_name AS nameb
 FROM civicrm_relationship AS s, civicrm_contact AS d
 WHERE  s.contact_id_b=d.id;

CREATE TEMPORARY TABLE all_relationships as
SELECT h.relationship_id AS r_id_a,h.rel_type AS rel_type_a,j.relationship_id AS r_id_b,h.namea AS name_a,j.nameb AS name_b
FROM relationshipa AS h,relationshipb AS  j
WHERE  h.relationship_id=j.relationship_id;

SELECT k.r_id_a AS r_id_a,k.r_id_b AS r_id_b,k.name_a as name_a,k.name_b AS name_b,k.rel_type_a AS reltype,
                             l.id AS reltype_id,l.name_a_b AS rolenamea,l.id AS reltype_id,l.name_b_a AS rolenameb
FROM  all_relationships AS k,civicrm_relationship_type AS l
WHERE k.rel_type_a=l.id
order by k.name_a
This query will just list individuals and their relationships to households. You can specify any other relationship by changing the number for relationship_type_id in lines 7 and 10.

 DROP TABLE IF EXISTS relationshipa;
DROP TABLE IF EXISTS relationshipb;
DROP TABLE IF EXISTS all_relationships;
CREATE  TABLE relationshipa as
 SELECT r.contact_id_a AS contact_a,r.id AS relationship_id,r.relationship_type_id AS rel_type,c.id AS contact_id,c.sort_name AS namea
 FROM civicrm_relationship AS r, civicrm_contact AS c
 WHERE  r.contact_id_a=c.id  AND r.relationship_type_id=7 ;

 
CREATE  TABLE relationshipb as
 SELECT s.contact_id_b AS contact_b,s.id AS relationship_id,s.relationship_type_id AS rel_type,d.id AS contact_id,d.sort_name AS nameb
 FROM civicrm_relationship AS s, civicrm_contact AS d
 WHERE  s.contact_id_b=d.id AND s.relationship_type_id=7 ;

CREATE  TABLE all_relationships as
SELECT h.relationship_id AS r_id_a,h.rel_type AS rel_type_a,j.relationship_id AS r_id_b,h.namea AS name_a,j.nameb AS name_b
FROM relationshipa AS h,relationshipb AS  j
WHERE  h.relationship_id=j.relationship_id;

SELECT k.r_id_a AS r_id_a,k.r_id_b AS r_id_b,k.name_a as name_a,k.name_b AS name_b,k.rel_type_a AS reltype,
                             l.id AS reltype_id,l.name_a_b AS rolenamea,l.id AS reltype_id,l.name_b_a AS rolenameb
FROM  all_relationships AS k,civicrm_relationship_type AS l
WHERE k.rel_type_a=l.id
order by k.name_b