function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
PBS_IC786PBS_IC786 

SOQL Query Help -- multiple joins

How can I structure a statement so that I can pull data from fields in 3 different custom objects which have a lookup relationship with 1 custom object?  Also, once I do that, how can I hold that dataset and do calculations against it before presenting the results on an scontrol?
 
Any help is greatly appreciated...
Ron HessRon Hess
using the eclipse toolkit, you can go the the schema viewer, and open the objects that you want to look at, then open the field list and click the check boxes.  the query will be built for you pulling in the fields that you asked for. I believe this works for multiple custom objects which are related.
look in the child-relationships to pull those in.
PBS_IC786PBS_IC786

Thank you Ron.  That was helpful but now I have a new dilemna.  Does soql support aggregation?  I can't seem to find any documentation on a sum() function.  I would like to sum all of the values returned for one of my custom objects' fields...any way to do this?

Any help is greatly appreciated...
Ron HessRon Hess
no sum() function, you can just walk the array once it's in memory and add them up.
least wise thats what i do.

we have count (counts records on a query)  orderby and limit
no  sum or unique or ...
PBS_IC786PBS_IC786
I was hoping you weren't gonna say that but thank you for the help...
Again AgainAgain Again
Should I be able to do a query like

Select Name, Parent.Name, Parent.Id, ParentId FROM Attachment WHERE Parent.Id = 'xxx'

using the eclipse schema viewer?

I get an error if I try, but the query does run correctly in my s-control.

I can run a query like Select Name, ParentId, Id FROM Attachment LIMIT 10, but that's not really what I'm going for.
cheenathcheenath
I tried this out in AJAX Tools:

SELECT Name, Parent.Id, Parent.Name, ParentId FROM Attachment where ParentId = 'xxx'

Above query seems to be fine. But the one bellow is failing:

SELECT Name, Parent.Id, Parent.Name, ParentId FROM Attachment where Parent.Id = 'xxx'

with error: MALFORMED_QUERY: Illegal filter on Attachment
Again AgainAgain Again

Yes, I see now I ended up changing the query to

Select Name, Id, Parent.Name, Parent.Id, ParentId FROM Attachment Where Parent.Name = 'xx'.

I'm still curious whether the Eclipse explorer would do a query like this though, because I get a java.lang.reflect.targetInvocation error when I try to run queries that use the SOQL joins.
Again AgainAgain Again
Hmm, it's odd, when logged in as a user, I get an invalidfilter error when I try to filter using Parent.Name or Parent.Id.  When logged in as admin, there is no error.
Vivek ViswanathVivek Viswanath
Hi Ron,

Is the sum() function introduced as this really helps, there is a huge lag time if you are waiting to load all records into memory espicially if you have 10000 to 15000 records

Regards

Vivek Viswanathan
Associate Consultant-CRM Solutions Group
STC StraightThrough Inc.
knicholsknichols
Did you get an answer to this?  I just hit the same problem.  I can login as admin and everything works fine with a query like this:
Select a.Id, a.Name, a.Owner.LastName, a.Owner.FirstName From Attachment a where a.ParentId='a0I500000016QTG' and a.Owner.Id in(ids) order by a.Owner.LastName

But when someone else is logged in, it doesn't work.
Vivek V.ax312Vivek V.ax312
Please check the users credentials he has to be  API Enabled apart from this please check the accibility on the object.
 
Regards
 
Vivek