CiviCRM Community Forums (archive)

You could use the API chaining for Gets to, so you should be albe to have a Get for the Memberships and the households. You can not join the two gets in one API call.
I would certainly not recommend doing the direct SQL for all the obvious reasons. If you feel that the API is insufficient you could use

$mySelect = "SELECT fielda, fieldb FROM table1 JOIN table2 ......";
$daoResult = CRM_Core_DAO::executeQuery( $mySelect);
while ($daoResult->fetch() ) {
   $field = $daoResult-fielda;
}

so that you at least use the CiviCRM DAO.

OK, cancel that request. I should have just tried it with Get replacing the Create, instead of worrying if it would work, which of course it does.

For the record (at least until someone can point out some more formal documentation on Chaining Gets), the Chained API call returns an array of results from the first Get, with each of the Keys being used as input to the second Get to produce another array of results.

The Key from the first array can therefore be cross-referenced by PHP to the second array (effectively as a Foreign Key).

Sorry, if this is blatantly obvious to most, but it took a while for me to get my head around the Chaining process.

<quote>
For the record (at least until someone can point out some more formal documentation on Chaining Gets), the Chained API call returns an array of results from the first Get, with each of the Keys being used as input to the second Get to produce another array of results.</quote>

Could you amend the wiki to clarify? seems we failed detailing it.

Please keep in mind that the chained api means that for each "master" entity returned from the initial get, it will generate the chained request, so if you have 20 results, you will have 21 api calls.

Can you do the request you want directly in the advanced search? if it's is the case, a
<code>
civicrm_api("contact","get",array (contact_type=>Household, whatever_filter_on_membership => the thing you want
return=array("sortname",...)
)
</code>
should work