Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
khanWebgurukhanWebguru 

Please help me in SOQL 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 :smileysad:... 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.

Best Answer chosen by Admin (Salesforce Developers) 
wesnoltewesnolte

Hey

 

As was mentioned before you probably shouldn't be using :filterby in this way. Although I can't immediately see the issue at hand I think it would be best if you did some debugging. This will help you locate the area causing the problem. You can debug by using System.debug in appropriate areas of your code eg. 

 

change this,

 

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

 

to this,

 

String qs =  select Id,Name,Product_Group__c from Product2 where Name Like :filterby OR Name Like \'%'+filterby+'%\'  order by ' + sortFullExp;

System.debug('Query string is: '+ qs);

results = database.query(qs);

System.debug(qs);

 

There is the possibility that your query isn't directly the issue. So include debug messages throughout your code.

 

Cheeres,

Wes 

All Answers

wesnoltewesnolte

Hey

 

As was mentioned before you probably shouldn't be using :filterby in this way. Although I can't immediately see the issue at hand I think it would be best if you did some debugging. This will help you locate the area causing the problem. You can debug by using System.debug in appropriate areas of your code eg. 

 

change this,

 

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

 

to this,

 

String qs =  select Id,Name,Product_Group__c from Product2 where Name Like :filterby OR Name Like \'%'+filterby+'%\'  order by ' + sortFullExp;

System.debug('Query string is: '+ qs);

results = database.query(qs);

System.debug(qs);

 

There is the possibility that your query isn't directly the issue. So include debug messages throughout your code.

 

Cheeres,

Wes 

This was selected as the best answer
acl5acl5

Wes -

 

You note that :filterby shouldn't be used in the select query, can you elaborate on that?  Or reference any other discussion threads that do.  I'm pretty inexperienced with Apex and I have certainly used the :variable notation in database queries.  It would be helpful to better understand the most preferred method for this.

 

thanks!
wesnoltewesnolte

 Hey

 

You're combining two methods of querying, one is used by the api/dynamic soql and the other is vanilla soql in Apex e.g the first kind is,

 

String myValue ='one';

 

List<MyObject__c> objs = Database.query('SELECT id FROM MyObject__c WHERE field__c = '+myValue);

 

The second usage is something like

 

String myValue ='one';

List<MyOBject__c> objs = [SELECT id FROM MyObject__c WHERE field=:myValue];

 

The fact that it works when you combine them is strange. As you can see in this post: http://community.salesforce.com/sforce/board/message?board.id=apex&thread.id=7260, it should be causing an error.

 

Documents on dynamic SOQL: http://blog.sforce.com/sforce/2008/09/dynamic-soql--.html

Documents on vanilla SOQL:

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql.htm?SearchType=Stem

 

Cheers,

Wes

 
khanWebgurukhanWebguru

Dear Wesnolte,

 

Thanks for your replies I know about that what you are explaning I tried both saperately and togather and others all possible ways which are suggested by some blog users but its very strange I am still at same point.........Like Query running fine and in workingcondition the only problem I have that in some scnarios its not returning correct result as I already posted that I have an entry like "MQX" but when I find its not coming......But if I find " MQX" its returning records but not the exact matches but returnig "Arc MQX", "A MQX Series"......... Hope u r understanding my points.,...... Thanks for being with me

 

 

Regards,

 

Asif Ahmed Khan
wesnoltewesnolte

Hey

 

That is strange. Well first of try this and check the debug logs to ensure your query looks the way you expect it to:

 

String qs = 'select Id,Name,Product_Group__c from Product2 where Name Like \'%'+filterby+'%\'  order by ' + sortFullExp;

System.debug('Querystring: '+qs);

 

results = Database.query(qs);

 

Let me knwo if you don't know where to find the debug logs.

 

Wes

khanWebgurukhanWebguru

Dear Wesnolte,

 

I used APEX DATA LOADER and paste my query over there very strange its working 100% fine I printed final query Which I built and run in APEX DATA LOADER in export mode.... I don't know why its not returning on apex page :(

 

Regards,

Asif Ahmed KHan