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
VivoVivo 

Querying based on objects in related list

Hi,

 

Right now I have a query that looks like:

select id,name, Description__c, Enabled__c,(SELECT name,id,function__c,scenario__c from functions__r order by name) from Scenario__c where Scenario__c.name != null order by Name asc limit 20

 

but I would like to add a clause to it that let's me filter out Scenarios that contain certain functions in it's funcrions__r related list.

 

I thought I would do it like:

select id,name, Description__c, Enabled__c,(SELECT name,id,function__c,scenario__c from functions__r order by name) from Scenario__c where Scenario__c.name != null and functions__r.name LIKE 'Name%' order by Name asc limit 20

 

but it does not seem to be working. Has anyone dealt with querying from within a queried list like this? How should it be done?

 

 

Thanks

Sean TanSean Tan

I'm not sure why you're filtering out scenarios based off the Name not being null when you're querying on the Scenario table (Name can never be null). Regardless something like this give you what you're looking for (the table name may be wrong but you can correct anything you need).

 

SELECT id,name, Description__c, Enabled__c, (SELECT name,id,function__c,scenario__c from functions__r order by name) from Scenario__c where Id IN (SELECT Scenario__c FROM Function__c WHERE Name LIKE 'Name%') order by Name asc limit 20

 
sandeep@Salesforcesandeep@Salesforce

Use moe optimized following SOQL : 

 

[select id,name, Description__c, Enabled__c,(SELECT name,id,function__c,scenario__c from functions__r order by name) from Scenario__c where Scenario__c.name != null AND id in  (select id from yourchildObjectAPIName where name LIKE 'Name%') order by Name asc limit 20 ]

 

because I am not your child Object API name is functiona__c or some thing else so please put yout child obejc API name in place of "yourchildObjectAPIName"

 

Please let me know for further query.

 
VivoVivo

Hmm it doesn't seem to be working quite right. I'm not sure what I am doing wrong.

 

For reference: Scenario__c is the custom object I am querying from and functions__r is the related list within Scenario__c that ties it to it's child Function__c custom object.

 

I am attempting to query up all the Scenario__c objects that contain, for example Test1, Function__c object in it's related list.

 

Let's say Test1 is the name of the Function__c object that I require. How would you construct the SOQL to find all Scenario__c's with Test1 in it's related list?

 

 I tried this, but it did not work.

 

SELECT id,name, Description__c, Enabled__c, (SELECT name,id,function__c,scenario__c from functions__r order by name) from Scenario__c where Id IN (SELECT Function__c .name FROM Functions__r WHERE Name LIKE 'Test1%') order by Name asc limit 20
Sean TanSean Tan

Something like this should work:

 

SELECT id,name, Description__c, Enabled__c, (SELECT name,id,function__c,scenario__c from functions__r order by name) from Scenario__c where Id IN (SELECT Scenario__c FROM Function__c WHERE Name LIKE 'Test1%') order by Name asc limit 20