Author Topic: Problem getting chained membership data (Read 668 times)

wingfingerbill · August 07, 2012, 03:13:01 am

Hi

Trying to get all individual contacts with more than 1 membership entry.

$params = array(
		'contact_is_deleted' => 0,
		'contact_type' => 'Individual',
		'api.membership.get' => array('count' =>2),
		'version' =>3, 
		rowCount => -1
	);
	
	$contacts = civicrm_api('Contact','Get', $params); // 
	print_r($contacts);

As you can see, I don't know how to request results where count > 1, so I'm using count==2 instead.

Main problem, though, is that I'm getting all the individual (non-deleted) contacts back - together with their memberships info. How do I limit the results based on number of memberships?

Thanks

Eileen · August 07, 2012, 01:51:48 pm

I can't see a way to do this from the API - you're looking to do a query ending in

HAVING count => 2 which I can't see how to do - the api doesn't really do GROUP BY queries.

wingfingerbill · August 07, 2012, 02:19:10 pm

Quote

I can't see a way to do this from the API - you're looking to do a query ending in

HAVING count => 2 which I can't see how to do - the api doesn't really do GROUP BY queries.

OK. I'll do it the long-winded way.

Thanks anyway

petednz · August 07, 2012, 07:36:31 pm

eileen used this on one of our sites to give me a list of people with more than one membership so am sharing it for anyone looking for an sql but it may not help if you are then trying to use the API

Code: [Select]

ELECT no_memberships, m.contact_id, display_name, m.id as membership_id, mt.name, start_date, join_date, status_id, end_date  FROM civicrm_membership m

LEFT JOIN civicrm_contact c on c.id = m.contact_id
LEFT JOIN civicrm_membership_type mt ON mt.id = m.membership_type_id 
INNER JOIN (
SELECT count(*) as no_memberships, contact_id 
FROM civicrm_membership m 

GROUP BY contact_id
HAVING no_memberships > 1) as doubles
ON doubles.contact_id = c.id
ORDER BY c.id

wingfingerbill · August 08, 2012, 02:50:39 am

Thanks Pete

I might try the direct sql approach for next time - but I find sql a lot less intuitive/familiar than php, so it takes me longer to get my head round what's going on.

CiviCRM Community Forums (archive)

News:

Author Topic: Problem getting chained membership data (Read 668 times)

wingfingerbill

Problem getting chained membership data

Eileen

Re: Problem getting chained membership data

wingfingerbill

Re: Problem getting chained membership data

petednz

Re: Problem getting chained membership data

wingfingerbill

Re: Problem getting chained membership data