+ Start a Discussion
khanWebgurukhanWebguru 

Facing Problem in Like Query

I have Product object in salesforce and there are some products like 'MQX', 'ARC 600' etc

 

I am trying to get records through LIKE query like:

 

results = Database.query('select Id,Name,Product_Group__c from Product2 where Name Like :filterby OR Name Like \'%'+filterby+'%\'  order by ' + sortFullExp);

 

When I set filterby variable to "MQX" its not returning records :(... When I tried tro get records for "ARC"also givinig me NULL but when I ask for "600" then its returning fine also when I requested " MQX"  mean with one space as prefix also getting result but not getting exact match but getting like "Prodcut of MQX" etc......please have a look on following code

 

private void BindData(Integer newPageIndex)
    {
        try
        {
                string sortFullExp = sortExpression  + ' ' + sortDirection;
                searchText=sortFullExp;
                if(filterby == null || filterby == '')
                {                    
                      //searchText='Coming';
                }
                else if(filterby != '')    
                {
                       
                        results = Database.query('select Id,Name,Product_Group__c from Product2 where Name Like :filterby OR Name Like \'%'+filterby+'%\'  order by ' + sortFullExp);
                      
                      
                    
                }                         
    
            pageResults = new List<Product2>();
            Transient Integer counter = 0;
            Transient Integer min = 0;
            Transient Integer max = 0;
            if (newPageIndex > pageNumber)
            {
                min = pageNumber * pageSize;
                max = newPageIndex * pageSize;
            }
            else
            {
                max = newPageIndex * pageSize;
                min = max - pageSize;
                //min = (min <>
            }
            for(Product2 a : results)
            {    
                counter++;
                if (counter > min && counter <= max)
                    pageResults.add(a);
            }
            pageNumber = newPageIndex;
            if (pageResults == null || pageResults.size() <= 0)
            {
                ApexPages.addmessage(new ApexPages.message(ApexPages.severity.INFO,'Data not available for this view.'));
               results=null;
               pageResults=null;
            }
        }
        catch(Exception ex)
        {
            ApexPages.addmessage(new ApexPages.message(ApexPages.severity.FATAL,ex.getMessage()));
        }
    }

 

I have 7000 products in my object. Please help me in this. I want to get actual records for LIKE query.. Please reply ASAP Thanks in advance.

 

wesnoltewesnolte

Hey

 

Make your query simpler and then build it up eg. start with this

 

results = Database.query('select Id,Name,Product_Group__c from Product2 where Name Like \'%'+filterby+'%\' '); 

 

Cheers,

Wes 

XactiumBenXactiumBen

I think you're mixing static SOQL statements with the dynamic SOQL statements.

 

Static SOQL statements require :varName but dynamic ones require 'start of query' + varName + 'end of query'.

 

Try changing your statement to this:

results = Database.query('select Id,Name,Product_Group__c from Product2 where Name Like \'' + filterby + '\' OR Name Like \'%'+filterby+'%\' order by ' + sortFullExp);

 

khanWebgurukhanWebguru

Dear Wesnolts,XactiumBen

 

Thnaks for your response. I already tried in both of these ways and many other ways but my problem is still there :(

 

These query searching records in some cases like If I find RTOS, RC 600 etc

 

but its not working  on some products like 'MQX', 'ARC 600' etc. I am not understanding what the hell is happening. Working fine in some cases and fail in some cases. Although I had exact match for MQX but still its not giving me correct results :(

 

Regards,

 

Asif Ahmed Khan