+ Start a Discussion

More on SOQL and overall architecture

I'm relatively new to Apex, but have already developed a very complicated app in visualforce/apex.


Well, after working with SOQL for the past six months, I'm convinced I'm either still doing something terribly wrong, or SOQL is a bad joke. Please help me understand what I'm missing.


I have many objects that all relate to one another through lookup fields, sometimes one to many, others one to one, others many to many.


For example, object1 has a lookup to object2. So, if I get object1, and I want to then retrieve other fields in the associated object2, I then have to use soql and Object2 = [select id,name,other_field__c from object2 where id=:obj2id];


We do this all over the place in our app, in literally every class.


I have basically discovered that in order to use SOQL, I can't use the "where" clause inline in a loop without hitting SOQL limits. So I have completely stopped using the where clause.


Instead, I have a utility class where I query from the database once in the beginning (using soql) all of the records for any objects we're going to need, and then I iterate through them using a for loop, and look for the field with the attribute I want. Occasionally we have hit "too many statement" limits using this approach, but it seems like it allows us at least to write a functioning app.


It feels rather absurd to do this (and almost like cheating, but moreso doing something outrageously ridiculous from a programming perspective), but we haven't been able to figure any way around this architecture.



Thanks in advance!




Did you try using the relationship. eg.


if object1 and object2 are related via Lookup then you can query something like


select Id,Name,Object2__r.Name,Object2__r


when you use this __r relationship join automatically takes place.


if you one to many relationships you can do something like.


select Id,Name,(select Id,Name from Contacts) from Account 


here in an Account can be related to many contacts.