You need to sign in to do that
Don't have an account?
brad4d
Problem with SOQL relationship queries
I'm having some trouble navigating relationships within a SOQL query. The following query works:
"Select Id, (Select Duration__c From PayCodeEdits__r) from Worker__c"
where as this one doesn't:
SELECT id, PayCodeEdits__r.Duration__c FROM Worker__c
problem is that I need to be able to filter the output on the relationship thusly:
SELECT id, PayCodeEdits__r.Duration__c FROM Worker__c Where PayCodeEdits__r.Duration__c = 4
that doesn't work and I can't figure out how to make that filter appear in the working syntax
"Select Id, (Select Duration__c From PayCodeEdits__r) from Worker__c"
where as this one doesn't:
SELECT id, PayCodeEdits__r.Duration__c FROM Worker__c
problem is that I need to be able to filter the output on the relationship thusly:
SELECT id, PayCodeEdits__r.Duration__c FROM Worker__c Where PayCodeEdits__r.Duration__c = 4
that doesn't work and I can't figure out how to make that filter appear in the working syntax
Hope this helps.
All Answers
Putting a WHERE clause in the sub-select would limit the child records returned for each parent.
Could you use something like
SELECT Id, Worker__r.Id from PayCodeEdits__c p WHERE p.Duration__c = 4
?
Hope this helps.
name child
fred sammy
george jenny
the children records might be this
name subject grade
sammy English A
sammy German B
Jenny Juggling C
Jenny Violin B
What I want to get back is all parent records where there is a child record that has a grade of A
From the queries that I can construct so far I will be returned all parent records but the record returned for parent george will have no child records
thus:
Fred
Sammy A
George
null
This is going to require me to filter the George record out of the results set in my code before I do what I need to do with it.
Good luck with this solution.
This is the query without conditions which returns many records that I need to filter in my code
Select w.Id, (Select Duration__c, End__c, Id, Start__c From PayCodeEdits__r), (Select Direction__c, Id, Timestamp__c From Punches__r), (Select Dirtied__c, End_Date__c, Id, Start_Date__c From Timecards__r) from Worker__c w
What I would like to use as a filter is Timecards__r.Dirtied__c!=null. If I run the above query I need to walk the result set and eliminate (or ignore) all records that don't pass the filter.Something like this pseudo code
For (all in resultset.rs)
If RS[n].timecard.dirtied!=null
Do something useful
In almost all use-cases in my application I will be processing a very small minority of the returned records. So I will be running this huge result set of thousands of worker records only to find the few dozen who have edited their timecard since the last time I ran this process. If I were only concerned with Timecards I might be able to query from the child as you recommend but then I would lose the related fields of PaycodeEdits and Punches
Hope this helps.
Something like
Where worker.id in (select workerid from timecard where timecard.dirtied!=null)
That would get me where I need to be.
I don't want to bring back thousands of records to process dozens and I don't want to make a query per record to get the information I need. Either way I run in to your limits. In the first case I unnecessarily run into our record limits and in the second case I run into your # of query limits for each event. It should be possible to bring back only the records I need in the single query.
Please help.
1 . no, you can't filter parent rows based on child rows
2. no, you can't use a subquery in the where clause.
You're not going to be able to do this in a single query, just break it up into 2, query the child table to get the set of parents you're interested in, then run the main query with a in clause on the set of ids. If you're worried about round tripping a lot of data between the 2 queries, wrap them up into an apex webservice and call that. (which can do the 2 steps all server side, and just return you the results)
This method, select the children first, then use and IN statement for the Parents, has a fatal flaw in that the SOQL statements have a 10,000 character limit. This sounds like alot, but it is not.
I want to pull back Opportunities based on a Partner being shared to the Opp. Large partners have many opps. at 18 characters per OppID, the 10,000 characters gets chewed up in about 515 OpportunityID records to place in the IN statement.
The real issue is that I want to pull back Opportunity_Product, with Opportunity INfo, where a particular Partner person is shared. So it is 2 children to the Parent one of which controls the Overall Records.
DOES ANYONE have any other suggestions here to accomplish this in SOQL rather than in the code sending multiple SOQL statements through to get the same results?
I've encountered a similar problem and it seems it is a limitation by Salesforce.
We need to re-think the objective from another perspective and find another way to re-organize the data to get what we want.
Best Regards,
Kerwin