Advanced Search

[angelalaw]

I would like to adjust one thing on the advanced search

Make Keywords be able to do all or some. (AND or OR). As of right now it does exact phrase.

So, I know I have to modify advanced searches binding procedures and create one of my own.

I am assuming that I have to write my own special Select Statement to do this and not use the api call you guys are using.

I guess the question I have is there a DAL in your api that connects to the database and runs a query?

I was looking through the DataClient and it didn't seem like there was. So I wasn't sure if this is exposed to the outside user at all in the dlls you provide.

If not I can integrate my own but I rather not if you have a way of doing it.

Thanks,

Angela

[jmestep]

Yes, you would have to use a custom query. There are samples of a DAL at wiki.ablecommerce.com and mazhar has just posted some templates to use to create a DAL.

[mazhar]

Here are the topics from WIKI
http://wiki.ablecommerce.com/index.php/ ... cess_Layer
http://wiki.ablecommerce.com/index.php/Custom_Queries

You can download the DAL generation templates from here
viewtopic.php?f=47&t=9530

[angelalaw]

Hi Mazhar and Judy,

So, realistically there is not a DAL class library set up.

Meaning I need to hand code all the c# database objects from system.data myself.

This is fine I can create a DAL but I just want to make sure I am not interpretting the informational links, that I read, given in the post.

A DAL to me is something like this (in its most simplistic form). This is an example of a very very simplisitc DAL...

Code: Select all

public class data
{   

    public string sConnect;
    public data()
    {
        string sPhysicalPath = HttpContext.Current.Server.MapPath("~/thedb");
        sConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + sPhysicalPath + "\\mydb.mdb; User Id=; Password=";
    }
    public DataSet getDataSet(string sSQL)
    {
        DataSet dsTemp = new DataSet();
        try
        {
            using (OleDbDataAdapter daTemp = new OleDbDataAdapter(sSQL, sConnect))
            {
                daTemp.Fill(dsTemp);
            }
        }
        catch (OleDbException e)
        {
            ClassError(e, sSQL);
        }
        return dsTemp;
    }
    public DataTable getDataTable(string sSQL)
    {
        DataTable dtTemp = new DataTable();
        try
        {
            using (OleDbDataAdapter daTemp = new OleDbDataAdapter(sSQL, sConnect))
            {
                daTemp.Fill(dtTemp);
            }
        }
        catch (OleDbException e)
        {
            ClassError(e, sSQL);

        }
        return dtTemp;
    }
    public string cleanSQL(string sText, string sType)
    {
        Int32 iOut;
        switch (sType.ToLower())
        {
            case "n":
                Int32.TryParse(sText, out iOut);
                return iOut.ToString();
            case "a":
                sText.Replace(")", " ");
                sText.Replace("(", " ");
                sText.Replace("--", " ");
                sText.Replace("==", " ");
                sText.Replace(";", " ");
                sText.Replace("%", " ");
                sText.Replace("'", "''");
                return sText;
            default:
                return "";
        }
    }
    public bool executeNonQuery(string sSQL)
    {
        bool bRan = true;
        try
        {
            using (OleDbConnection oCon = new OleDbConnection(sConnect))
            {
                oCon.Open();
                try
                {
                    using (OleDbCommand oCmd = new OleDbCommand())
                    {
                        oCmd.Connection = oCon;
                        oCmd.CommandText = sSQL;
                        oCmd.ExecuteNonQuery();
                    }
                }
                catch (OleDbException e)
                {
                    bRan = false;
                    ClassError(e, sSQL);
                }
                finally
                {
                    oCon.Close();
                }
            }
        }
        catch (OleDbException e)
        {
            bRan = false;
            ClassError(e, sSQL);

        }
        finally
        {

        }
        return bRan;
    }
    protected void ClassError(Exception ex, string sSQL)
    {
        string sBody = "MESSAGE: " + ex.Message +
          "\nSOURCE: " + ex.Source +
          "\nTARGETSITE: " + ex.TargetSite +
          "\nSTACKTRACE: " + ex.StackTrace +
          "\nSQL String: " + sSQL +
          "\nConnection: " + sConnect;

        error.logError(sBody);
    }
}

is there a class like that?

[AbleMods]

And you said you weren't a techie geek.......

In AC7, a Data Access Layer is used for all connectivity to the DB using Microsoft Enterprise class libraries. However a DAL is not required.

In your case, you're going to need a function that can send the query you need to SQL and obtain the result set in a collection usable by the search page. Now that doesn't mean you have to write a full-out holy grail DAL. All you need is some sort of way to send the query to the SQL server and get the results into the same data class collection the existing search page uses.

The existing code that searches the SQL tables are actually methods within an existing data class DAL. If you own full source code, you could simply modify that class method to achieve the result you want. Otherwise, you're out of luck as those search methods are compiled in the DLL files.

You're going to have to write a brand new function that does the search and returns the results in the format expected by the search page. Not so simple if you don't know how Able is doing it now. Piece of cake if you own full source code.

The individual data source classes all have a common standard as you've probably noticed. In the case of products, you could construct your own SQL command and send it to the SQL db using

Code: Select all

ProductDataSource.LoadforCriteria("SKU LIKE Joe* AND (Instock > 0 OR VendorId=4)")

There is a short Wiki article that describes how to send SQL commands of any sort directly to the SQL engine. You can find it here: http://wiki.ablecommerce.com/index.php/Custom_Queries

[mazhar]

Advance search supports the wild cards. What about using the wild cards for your search pattern, have you tried them?

[angelalaw]

Hey Master Yoda,

Thanks for replying!

Well I guess I would not consider myself a geek Just a software developer (solid programmer) but not a guru. I would call my hubby a geek

That is exactly what I was looking for with what you provided I should be able to work something with the productdatasource.

So thanks for taking time to write something for me it helped a lot.

Much rather not add to much of my own code if it is not necessary. The shopping cart software is just about perfect but does need minor tweaks.

Before I purchase want to make sure I can program everything needed.

So I am going to try your solution now

Thanks alot

[angelalaw]

hi mazhar,

Thanks so much for replying.

Unforunately wild cards don't beleive will work for my purposes.

As the problem is I want it to do the following search

moonstone pendant

I want it to do a search like

'moonstone' and 'pendant'

right now the search does 'moonstone pendant'

What is useful about allowing all or some of the words is one better search results for the customer. The second is SEO stuff. Putting out links via PPC allows me to advertise Moonstone Pendants and give it the URL of the Search page with that search in the criteria.

thanks for chiming in

[mazhar]

hi mazhar,

Thanks so much for replying.

Unforunately wild cards don't beleive will work for my purposes.

As the problem is I want it to do the following search

moonstone pendant

I want it to do a search like

'moonstone' and 'pendant'

right now the search does 'moonstone pendant'

What is useful about allowing all or some of the words is one better search results for the customer. The second is SEO stuff. Putting out links via PPC allows me to advertise Moonstone Pendants and give it the URL of the Search page with that search in the criteria.

thanks for chiming in

Well I think there is a way you can make the advance search work for words. All you need is to adjust the AdvanceSearch page in such a way that when user enters multiple words then in back end system searches for each single word and then shows the combined search results. This would need changes in the advance search page, no back end or data layer changes.

[mazhar]

The suggested modification could be something like the modified version of advance search page in attachment.

[TheChris]

I am also working on a client's site where we would like the search to function "as it should" looking for all words in the search terms instead of matching the exact phrase as it does now.

We had entertained the idea of using the keywords field of the products table, but it seems this is useless as the search still has to match the exact phrase.

I am considering writing some new functionality into the advanced search control to use a custom query as mentioned above, but before I do that I wanted to find out more about the wildcard that was mentioned above. What is the wildcard? Is it an asterisk (*), or is it something else like the %? I haven't had any luck getting either to work. Also, if I can get the wildcard to work would that even fix the issue?

Searching for "adidas shoe black", replace the spaces with wildcards: "adidas*shoe*black" would return "Adidas Men's Shoe - Black & White" or "Adidas Women's Shoe - White & Black". But I don't think it would work if I searched for "adidas black shoe", as the word black appears after the word shoe, not before. If this is the case, then I definitely need to write a custom query. eg. SELECT... WHERE ... LIKE "%adidas%" AND LIKE "%black%" AND LIKE "%shoe%".

I have looked at the wiki @ http://wiki.ablecommerce.com/index.php/Custom_Queries

My question is which method would work better for this situation? I am leaning towards the LoadForCriteria Method of the productdatasource rather than accessing the database object. Is there anywhere I can view some documentation for the syntax of the LoadForCritera method. I can see how to compare one field, but I'm not sure how to compare multiple fields with multiple search terms.

[AbleMods]

Is there anywhere I can view some documentation for the syntax of the LoadForCritera method. I can see how to compare one field, but I'm not sure how to compare multiple fields with multiple search terms.

The LoadforCriteria method just allows the developer to create their own WHERE clause within the SQL command sent to the database. Any standard T-SQL commands permissible in a WHERE clause would be possible. I don't have SQL docs handy, but there's tons of resources on the web for advanced SQL WHERE syntax. Google it and you'll find a bunch of examples.

[mazhar]

I am also working on a client's site where we would like the search to function "as it should" looking for all words in the search terms instead of matching the exact phrase as it does now.

We had entertained the idea of using the keywords field of the products table, but it seems this is useless as the search still has to match the exact phrase.

I am considering writing some new functionality into the advanced search control to use a custom query as mentioned above, but before I do that I wanted to find out more about the wildcard that was mentioned above. What is the wildcard? Is it an asterisk (*), or is it something else like the %? I haven't had any luck getting either to work. Also, if I can get the wildcard to work would that even fix the issue?

Searching for "adidas shoe black", replace the spaces with wildcards: "adidas*shoe*black" would return "Adidas Men's Shoe - Black & White" or "Adidas Women's Shoe - White & Black". But I don't think it would work if I searched for "adidas black shoe", as the word black appears after the word shoe, not before. If this is the case, then I definitely need to write a custom query. eg. SELECT... WHERE ... LIKE "%adidas%" AND LIKE "%black%" AND LIKE "%shoe%".

I have looked at the wiki @ http://wiki.ablecommerce.com/index.php/Custom_Queries

My question is which method would work better for this situation? I am leaning towards the LoadForCriteria Method of the productdatasource rather than accessing the database object. Is there anywhere I can view some documentation for the syntax of the LoadForCritera method. I can see how to compare one field, but I'm not sure how to compare multiple fields with multiple search terms.

You can read about custom queries and criteria method from here
http://wiki.ablecommerce.com/index.php/Custom_Queries

[mazhar]

The wild card * means any character and no limit for occurring of the character. The ? wild card means any one character. For example if you want to search the words that contains 'a' as their second character then the search pattern would be something as below.
?a*.

I don't think so that it will workout for the words search. If you want to write your own advance search module then you can give a try to LoadForCriteria. You can use all the column names of ac_Products table with Load for criteria. One limitation with the LoadForCriteria is that its built for simple queries you can not run complex queries such for example queries with joins etc.