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
Jonathan Wolff 7Jonathan Wolff 7 

No results for SOSL List with where clauses

Hello, 
I try to build a Searchbar component to give out a List of custom object (Mediathek__c) records. I try to use an sosl to use a keyword and show the results. Unfortunatly, I do not get any records for my searchKey, even tough the debug says, tat the searchkey enters right into the apex class. Could you help me to improve my code to get a proper List. In addition I get the error: 'unexpected toke:Find' in my debug.
  
public with sharing class MediathekSearchController {
    

    @AuraEnabled(cacheable=true)
    public static List<sObject> getSearchResult(String searchKey, String TypValue, String ZielgruppeValue, String ThemaValue){
        String findStr = '*'+searchKey+'*';
        
        Boolean isEmptySearch = String.isEmpty(searchKey);
        
       system.debug('Das searchKey ist: ' + findStr);
      String searchResult2 = 'FIND :findStr IN ALL FIELDS RETURNING Mediathek__c (Id,  Thema__c, Mediathek_ID_Long__c, Name, Bezeichnung__c, ContentDocumentID__c, Typ__c, Zielgruppe__c, Umfang__c, Bezeichnung_Link__c, Bezeichnung_Search__c WHERE Thema__c=:ThemaValue AND Typ__c=:TypValue AND Zielgruppe__c=:ZielgruppeValue AND Inaktiv__c=false)';
    
        system.debug('Das searchResult2 ist: ' + searchResult2);
      
      List <String> WhereClauses = new List <String>();
         IF (searchKey !=null) {
            WhereClauses.Add(' WHERE (Bezeichnung__c  LIKE \'%' + searchKey + '%\' OR Typ__c LIKE \'%' + searchKey + '%\')');
            
        }
        IF (ThemaValue !=null && ThemaValue !='Alle') {
            WhereClauses.Add('Thema__c = \'' + ThemaValue + '\'');
        }
        IF (TypValue !=null && TypValue !='Alle') {
            WhereClauses.Add('Typ__c = \'' + TypValue + '\'');
        }
        IF (ZielgruppeValue !=null && ZielgruppeValue !='Alle') {
            WhereClauses.Add('Zielgruppe__c = \'' + ZielgruppeValue + '\'');
        }
   
        
         String WhereClause = String.join(WhereClauses, ' AND ');
           
  


//List<List<sObject>> searchResult = Search.query(SelectClause);
 

        
  String SQL = searchResult2 + WhereClause;
        system.debug('Der Query ist: ' + SQL); 
   List<sObject> searchResult = Database.query(searchResult2);
 	
	system.debug('searchKey ' + searchResult); 
    
     return searchResult;
    }

 
SubratSubrat (Salesforce Developers) 
Hello ,

n your code, you have a few issues that are preventing you from getting the desired search results. Here are the changes you need to make:

Issue with SOSL syntax: The error you encountered, "unexpected token: Find," is because you are using SOSL syntax within a SOQL query. SOSL and SOQL have different syntax structures. To perform a SOSL search, you should use the FIND statement separately from the query.
Use SOSL FIND statement: Instead of constructing a SOQL query, you need to use the SOSL FIND statement to perform the search. You can build the SOSL query dynamically based on the provided search criteria. Here's an example of how you can do it: 
@AuraEnabled(cacheable=true)
public static List<sObject> getSearchResult(String searchKey, String TypValue, String ZielgruppeValue, String ThemaValue) {
    String findStr = '' + searchKey + '';
    
    List<String> whereClauses = new List<String>();
    
    if (searchKey != null && !searchKey.isEmpty()) {
        whereClauses.add('Bezeichnung__c LIKE \'%' + searchKey + '%\'');
        whereClauses.add('Typ__c LIKE \'%' + searchKey + '%\'');
    }
    
    if (ThemaValue != null && ThemaValue != 'Alle') {
        whereClauses.add('Thema__c = \'' + ThemaValue + '\'');
    }
    if (TypValue != null && TypValue != 'Alle') {
        whereClauses.add('Typ__c = \'' + TypValue + '\'');
    }
    if (ZielgruppeValue != null && ZielgruppeValue != 'Alle') {
        whereClauses.add('Zielgruppe__c = \'' + ZielgruppeValue + '\'');
    }
    
    String whereClause = String.join(whereClauses, ' OR ');
    
    String searchQuery = 'FIND \'' + findStr + '\' IN ALL FIELDS RETURNING Mediathek_c(Id, Themac, Mediathek_ID_Longc, Name, Bezeichnungc, ContentDocumentIDc, Typc, Zielgruppec, Umfangc, Bezeichnung_Linkc, Bezeichnung_Search_c)';
    
    if (!whereClause.isEmpty()) {
        searchQuery += ' WHERE ' + whereClause;
    }
    
    List<List<SObject>> searchResult = [FIND :searchQuery];
    
    List<SObject> records = new List<SObject>();
    for (List<SObject> sobjects : searchResult) {
        records.addAll(sobjects);
    }
    
    system.debug('searchResult: ' + records);
    
    return records;
}

Hope this helps !
Thank you.