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

[EthanKranau]

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]

Simeon,

    If your are familiar with how to do that in PHP than you can do it in Flex to the issue is what actual code to change in the eSearch widget. So how do you plan to handle the ampersand in your example? Because when you split the string based on spaces you will end up with a list like this:

1. James


2. &


3. Heather


4. Black

Which version of the eSearch are you working with, before I spend any time looking at this in the wrong version?

[EthanKranau]

Robert,

I am using 2.5. I think I would probably state in the example text to only type names so the string would be: "James Heather Black". In php I would split the string and I would have:

array (
0 = James
1 = Heather
2 = Black
)

then do a for each loop for every item in the array and create the string: Owner LIKE %James% AND Owner LIKE %Heather% AND Owner LIKE %Black%

then I would add that to the SELECT statement and get the query for the parcels: SELECT * FROM parcels WHERE Owner LIKE %James% AND Owner LIKE %and% AND Owner LIKE %Heather% AND Owner LIKE %Black%

You could use OR statements or AND statements. Cons of the OR statments is that you will get every parcel that the owner fields contains one or more of those three strings, BUT you could allow for other characters if someone searches for "James and Heather Black". AND statements would be more accurate as long as the user doesn't type in anything that is not in the record.

I was looking through the query function in the eSearchWidget.mxml file, but I'm not sure how to go about it without ruining the widget.

Eventually this may be something you might think of adding in as an option.

[RobertScheitlin__GISP]

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:

[EthanKranau]

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]

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);