Need help thinking through how to aggregate contact info...

[lee.gooding]

Hopefully someone out here can help me think through this...

I am attempting to aggregate information for contacts. This includes information such as groups, tags, custom fields, relationships between, memberships, etc.

Maybe I am approaching this wrong, but it seems quite tedious to track down the information through the API. It seems like I'm getting IDs, then using those IDs to look up another set of information, which then gives me other IDs that I need to use for yet another API query. 

Is there any easier way to go about aggregating information using the API?

If not, wouldn't it just be much easier/faster to create an SQL view and then query that view? For example, I could do a query that returns rows for contacts that are in group X. That row would already have all the information that I might be interested in for that contact. I could simply pick and choose what I want to display from the row...

... OR would a combination be best? For example, for data that has multiple entries (such as activities and contributions) the API would be used in combination with a View?

I really appreciate any thoughts/insight into this.

Best,

Lee

[Coleman Watts]

It sounds like you already have your answer. A SQL view should work nicely for your use-case.
In general the CiviCRM API is not well-suited to generating complex reports that use a lot of joins. Not api3 anyway. There is some talk of doing this better in the next generation api, probably taking advantage of Doctrine's DQL, and we would welcome your involvement and input

[lee.gooding]

That's good to know!

I appreciate the response. That gives me confidence to move forward from here.

Can you point me in the right direction for getting involved and giving input in this area? I'll help where I can!

[xavier]

Another direction might be to write your own custom api, that will we running the magic query with as many joins as you want and returns it.

[lee.gooding]

Is there a particular advantage to this approach?

Another direction might be to write your own custom api, that will we running the magic query with as many joins as you want and returns it.

[xavier]

It provides you the wrapper to retrieve it from php rest ajax or smarty+permission. that might or might not be useful in your case.