Displaying multi-subquery, dynamic field, dynamic object SOQL query results on a VF page

Question

I want to pass a SOQL query to a page, or have the users enter it on the page themselves, process it, and then display results in a table.

Biggest problem is displaying the results, as VF dynamic binding only seems to be working one level deep, after that it gives null pointer exceptions (seems to be some bug in SF).

I have a dynamic main object with multiple related lists coming from the query, for example: user may be pulling a list of Accounts, with all related Contacts, and all related Opportunities. Here is an example query:

select id, name, BillingState, (select id, name, title from Contacts), (select id, amount from Opportunities) from Account where name like '%Corp%'

Another time, the query might be on a completely different parent object, like:

select id, name, accountId, (select id, Cost from OpportunityLineItems), (select id, name from Attachments) from Opportunitiy limit 20

It's not a problem parsing the field and object names from the query, but using dynamic binding for displaying those results in a table on a VF page is a nightmare, and is not working for me. Any ideas? or maybe you have seen VF code for this specific situation somewhere?

As a side note, below is the error i keep running into on the VF page when trying to use dynamic binding

Error: java.lang.NullPointerException
Error: null

Which part of dynamic binding is not working? Might be helpful if you post part of the visualforce where you are binding. Are you binding the right way, would be in the form Object__c['ChildRelationships__r'] — manubkk
I ended up using a wrapper class, per Adam's suggestion. Using dynamic binding by itself wouldn't do the trick. — Kirill Yunussov

Adam Adam · Accepted Answer · 2012-07-17T15:39:47

In these situations I always rely on an object relational mapper pattern, or referred to simply as a wrapper class. For the trickier fields, what you wind up displaying are single level, class member abstractions rather than fields directly from the result of a SOQL statement. One benefit of this is that you can simply do a few SOQL calls, prepare the data in any way that will best support your page and then render it smoothly. The additional work of having this abstraction class pays off very well in the end - imo.

Here's a related post that shows exactly how to do this in Apex. In your case, you would extend on this example to add the values of multiple SObjects to one instance of this "ORM-style" class and then populate a list of instances of it from within Apex. This list of custom object instances becomes perfect food for Visualforce to consume.

Displaying multi-subquery, dynamic field, dynamic object SOQL query results on a VF page

1 Answers