Anyway to do an OR sql query in the enhanced search widget?

Question

I want to do do an OR query using the enhanced search widget. I have a parcel layer that I want to make searchable by the owner name. But I want to make the search "smart" in the way that it will search all words in the string.

Example: if a parcel is owned by James & Heather Black, the parcel lists the owner in legal form, BLACK JAMES & HEATHER. So what I need is a query that does this: SELECT * FROM parcels WHERE Owner LIKE black OR Owner LIKE James OR Owner LIKE Heather.

In PHP I would split the string into an array by the space character and then through a loop create the SQL query. Is this possible in the eSearch widget?

I am somewhat of a Flex noob, but there has to be a way to do this.

Thanks for your time

RobertScheitlin__GISP · Answer

Simeon,

So are you needing help with the actual Flex equivalents to what you use to do in PHP? Like:var tArr:Array =txtSearch.text.split(" ");
var fSQL:String = "SELECT * FROM parcels WHERE ";
//Owner LIKE %James% AND Owner LIKE %and% AND Owner LIKE %Heather% AND Owner LIKE %Black% ";
for each (var val:String in tArr){
   fSQL += "Owner LIKE %" + val + "% AND";
}
//remove the last AND
fSQL = fSQL.substring(0, fSQL.length - 4);

EthanKranau · Answer

Thanks Robert... I will try that. I think I will copy the eSearchWidget and develop the copy so I don't break the whole thing.

I think I just need to learn Flex more. I used to build websites in Flash with action script 2. But that was years ago. Hopefully some of that will come back.

RobertScheitlin__GISP · Answer

Simeon,

You can definitely do this you would begin by putting the code in the queryFeaturesText function. I will not likely add this as it is pretty specific to your needs and you would not want to have a like query inserted all the time when MANY times most people are looking for an exact match query. Anyway you need to insert your addition in this block:                if ((queryLayer) && (txtSearch.text || cbSearch.text))
                {
                    var query:Query = new Query();
                    var myPattern:RegExp = /$$value$$/g;
                    var expr:String;
                    var eVal:String;
                    if (txtSearch.visible){
                        if(queryExpr != "[value]")//meaning an open SQL expression
                            eVal = txtSearch.text.replace("'","''");
                        else
                            eVal = txtSearch.text;
                        expr = queryExpr.replace(myPattern, eVal); 
                    }else{
                        eVal = cbSearch.selectedItem.value.replace("'","''");
                        expr = queryExpr.replace(myPattern, eVal); 
                    }
                    query.where = expr;

You would want to check if the queryExpr is the one that you are looking to replace (meaning check that it equals the one that you setup for your specific layer), you could go as far as assigning some bogus expression in the XML that you could check for. Notice in the code above I check for an open SQL expression you would want to do something like this:                        if(queryExpr == "your value"){
                             expr = mySQLBuilderFunction(txtSearch.text); 
                        }else if(queryExpr != "[value]"){//meaning an open SQL expression
                            eVal = txtSearch.text.replace("'","''");
                            expr = queryExpr.replace(myPattern, eVal); 
                        }else{
                            eVal = txtSearch.text;
                            expr = queryExpr.replace(myPattern, eVal); 
                        }

Then you build your private function to do the SQL Statement building.

Don't forget to click the Mark as answer check and to click the top arrow (promote) as shown below: