CODEWITHSUNDEEP
javascriptsql-likesoql
pceccon
pceccon

Reputation: 9844

LIKE or CONTAINS in SOQL Query

I'm trying obtain some data from the following URL, using a JavaScript code:

http://data.cityofnewyork.us/resource/erm2-nwe9.json

That's how I construct my query:

//data URL variables
var start_date = '2013-08-01'; //YYYY-MM-DD
var end_date = '2013-08-08';   //YYYY-MM-DD
var c_type = 'Noise';          // Complaint Type

// Build the data URL
var URL = "http://data.cityofnewyork.us/resource/erm2-nwe9.json"; // API Access Endpoint
URL += "?";                                                   
URL += "$where=";                                             
URL += "(latitude IS NOT NULL)";                              
URL += " AND ";
URL += "(complaint_type='" + c_type + "')";                    
URL += " AND ";
URL += "(created_date>='" + start_date + "') AND (created_date<='" + end_date + "')"; 
URL += "&$group=complaint_type,descriptor,latitude,longitude";                        
URL += "&$select=descriptor,latitude,longitude,complaint_type";                       
URL = encodeURI(URL);

And how I'm testing it so far:

$.getJSON(URL, function(data)
{
        console.log(data)
});

Right now it works fine, but I should consider any complaint type that contains a single world ("Noise"):

URL += "(complaint_type LIKE '%" + c_type + "%')";

Encoded URL (seems OK):

http://data.cityofnewyork.us/resource/erm2-nwe9.json?$where=(latitude%20IS%20NOT%20NULL)%20AND%20(complaint_type%20LIKE%20'%25Noise%25')%20AND%20(created_date%3E='2013-08-01')%20AND%20(created_date%3C='2013-08-08')&$group=complaint_type,descriptor,latitude,longitude&$select=descriptor,latitude,longitude,complaint_type

Error:

{
  "code" : "query.compiler.malformed",
  "error" : true,
  "message" : "Error, could not parse SoQL query \"select descriptor,latitude,longitude,complaint_type from #erm2-nwe9 where (latitude IS NOT NULL) AND (complaint_type LIKE '%Noise%') AND (created_date>='2013-08-01') AND (created_date<='2013-08-08') group by complaint_type,descriptor,latitude,longitude\"",
  "data" : {
    "query" : "select descriptor,latitude,longitude,complaint_type from #erm2-nwe9 where (latitude IS NOT NULL) AND (complaint_type LIKE '%Noise%') AND (created_date>='2013-08-01') AND (created_date<='2013-08-08') group by complaint_type,descriptor,latitude,longitude"
  }
}

The documentation seems that it is possible to use LIKE, but I can't get it to work.

I don't know how to do this.

Upvotes: 3

Views: 11796

Answers (1)

GSazheniuk
GSazheniuk

Reputation: 1384

I have just figured out how it works, it seems that it does not accept just percent symbols '%' in the condition, it should be preceded by backslash(or is it just slash, anyway).

So this is the valid URL for using 'like' statement:

http://data.cityofnewyork.us/resource/erm2-nwe9.json?$where=(latitude%20IS%20NOT%20NULL)%20AND%20(complaint_type%20like%20%27\%Noise\%%27)%20AND%20(created_date%3E=%272013-08-01%27)%20AND%20(created_date%3C=%272013-08-08%27)&$group=complaint_type,descriptor,latitude,longitude&$select=descriptor,latitude,longitude,complaint_type

Or appropriate row in your code:

URL += "(complaint_type LIKE '\\%" + c_type + "\\%')";

Let me know if it works, and sorry for not replying on time, I really have no experience with Salesforce and SOQL. But thanks to you there is another new space for me to explore :)

Upvotes: 1

Related Questions