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
MattMet86MattMet86 

SOQl - Filter on related field

I have a visualforce page passing a value to controller.
Variable name is stFilter
My soql is: 
Select Id, Name, User__r.Name, 
(Select Id,Account__c,Account__r.Name,Hub_Name__r.Name FROM Account_Counselors__r), 
(Select Name FROM Licenses__r) 
FROM BCS__c WHERE Inactive__C = FALSE
How do I filter it so that only BCS_c records that have a Licenses__r record with "Name" value matching stFilter are returned.

Example
stfilter = Kansas
I only want to get back BCS__c records that have a Licenses__r record for Kansas.
Best Answer chosen by MattMet86
MattMet86MattMet86
I figured it out. Thanks for getting me started.
  
Select Id, Name, User__r.Name,
                    (Select Id,Account__c,Account__r.Name,Hub_Name__r.Name
                     FROM Account_Counselors__r),
                    (Select Name 
                     FROM Licenses__r
                    )
                    FROM BCS__c
                    WHERE Inactive__C = FALSE AND 
                    id in (select Hub_Name__c from LIcense__c where Name = :stFilter)

 

All Answers

JeffreyStevensJeffreyStevens
I think  
Select Id, Name, User__r.Name, 
(Select Id,Account__c,Account__r.Name,Hub_Name__r.Name FROM Account_Counselors__r), 
(Select Name FROM Licenses__r) 
FROM BCS__c WHERE Inactive__C = FALSE AND Licenses__r.Name = :stFilter

If that doesn't work - then convert it all to a string, and do it like this...
  
string queryString = 'Select Id, Name, User__r.Name, ' + 
'(Select Id,Account__c,Account__r.Name,Hub_Name__r.Name FROM Account_Counselors__r), ' + 
'(Select Name FROM Licenses__r) ' + 
'FROM BCS__c WHERE Inactive__C = FALSE AND Licenses__r.Name = \'' + stFilter + '\'';

list<BCS__c> BCSs = database.query(queryString);

 
MattMet86MattMet86
@JeffreyStevens, I owe you many a beer for the help you are always giving me!

I'll give this a try in a bit. Right now I am trying to figure out why a Related List has vanished from a page layout in my Sandbox. The related list still shows in my production environment and I haven't made any layout changes in the sandbox so not sure what happened. 
 
MattMet86MattMet86
Jeff, I tried your way but I am getting an interesting error back. 

Didn't understand relationship 'Licenses__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

How does it not recognize the relationship...it recognized it in the field selection a few lines earlier? 
 
JeffreyStevensJeffreyStevens
Ok - is the object name License or Licenses?  If it's License - then the where clause should be ... AND License__r.Name = :stFilter.  In the sub-query it should be whatever the plural name was for the object setup - probably Licenses__r...

I probably lead you astray there.

BTW - it's funny how I see your post - sometimes very quickly - I'm not on the forum every day - but usually every week.
MattMet86MattMet86
I figured it out. Thanks for getting me started.
  
Select Id, Name, User__r.Name,
                    (Select Id,Account__c,Account__r.Name,Hub_Name__r.Name
                     FROM Account_Counselors__r),
                    (Select Name 
                     FROM Licenses__r
                    )
                    FROM BCS__c
                    WHERE Inactive__C = FALSE AND 
                    id in (select Hub_Name__c from LIcense__c where Name = :stFilter)

 
This was selected as the best answer