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
brad4dbrad4d 

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
Best Answer chosen by Admin (Salesforce Developers) 
RickyGRickyG
Sounds like the pseudo code has you on the right track.  Run a SOQL query to do the selection, get the parent, and then the multiple children.

Hope this helps.

All Answers

RickyGRickyG
Dot notation works for child to parent, while the sub-select is required for parent to child.  It seems like you are doing parent to child, so dot notation would not work.

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.
brad4dbrad4d
I am trying to retrieve all parents that have children that meet a criteria and only those children that meet that criteria.  I would not like to have to bring back unnecessary records by getting all parents and only the children that meet the criteria.  That would require me to filter the parent records which do not have children records in my code after getting the results.  Is there anyway to get the data engine to do this for me. 
brad4dbrad4d
So the parent records might be such  

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.
RickyGRickyG
As I mentioned in the previous answer, I think you want to base the SOQL statement on the child record, limit the selection of that record, and access the parent through dot notation.

Good luck with this solution.
brad4dbrad4d
The problem with that approach is that the actuall query I am trying to accomplish is a bit more complex.  There are three child records all linked together with the one parent record and I need to filter the set on the one child.

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
RickyGRickyG
Sounds like the pseudo code has you on the right track.  Run a SOQL query to do the selection, get the parent, and then the multiple children.

Hope this helps.
This was selected as the best answer
brad4dbrad4d
Is it possible to do a subselect in a where clause?

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.
SuperfellSuperfell
To answer some of the questions in the thread.
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)
MissouriAdminMissouriAdmin

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?
kerwintangkerwintang

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