Extend search with additonal tables/columns?

[kstevenson]

In our environment we vastly extend the data for each product by "sync'ing" product and product marketing information from the backend ERP system into tables we added to the database. The record are all linked with foreign keys to the ac_Products records and it's been working great (added ConLibs to pull that data into the product and search result pages as desired on each site). One of the issue we found with the search seems to be solved by implementing the solution in this post: viewtopic.php?f=42&t=12887&p=55535&hili ... rds#p55535

But in addition to that we are facing the task of extending the simple and advanced searches to include the data in the new tables and columns.

Does anybody have any advice? We are trying to get this done without customizing the CommerceBuilder source, it at all possible. One approach has been to attempt and generate a second search catalog that includes all the data we need (rebuild it nightly with the updated product data after the sync). That of course, has its own pitfalls, so I thought I pick the collective brain here on the forum before we continue chasing our proverbial tails.

Has anybody done something like this successfully in 7.0.x?

Thanks for any feedback and advice you can provide.

[HaterTot]

What are the pitfalls with having a second search catalog? Maybe you should make your own master catalog that includes all the tables and columns you need and circumvent AC's search functions altogether.

I have no idea if i'm helping or if i'm an idiot trying to explain to a pro. If the former is the case, here are some explanatory links
http://msdn.microsoft.com/en-us/library/ms142547.aspx -- how to set up Full Text Search
http://stackoverflow.com/questions/4036 ... es-columns

[heinscott]

The easiest way I have found to use additional tables of product info in the search is by using the LoadForCriteria in a creative way.

Example:

Orginal Table: ac_Products
Extended Table: ac_ProductsExtended (contains foreign key to ProductId)

ProductCollection pc = ProductDataSource.LoadForCriteria("ProductId IN (Select * from ac_ProductsExteded WHERE RelevantInformation='DesiredValue')");
This will allow you to choose only the items from Products table that match items in your ProductsExtended table that fit the criteria.

- Scott

[kstevenson]

Thanks to both of you for your feedback. The problem that complicates things so with the full text catalog is that you can't easily do searches spanning multiple tables. When I search with a multiple word keyword like "felt quilt hard cover" I need the system to find the one product that has "felt" in the product ExtendedDescription, "quilt" in a column in one of the new tables and "hard cover" in the product format field in yet another new table. On top of that the requirement is that the columns to be searched should be configurable per site upon site setup (and modifiable later) for each publishing customer.

So, I thought I check if somebody else on this forum already fought such a battle and is willing to share how they won it (defeat is not an option ). I welcome any other ideas on how to attack this.

Thanks again.

[kstevenson]

I forgot to add earlier why indexed views (to allow FTS queries across multiple tables) are not an option either. Quoting my developer "The thing is that after you add a table to an indexed view, the inserts that you do in that table must be preceded with some instructions. Take a look at this: http://www.codeproject.com/KB/database/ ... ndSQL.aspx . So we would need to modify all the places where new data is inserted in the tables referenced in the indexed view."

Obviously not an option.

I'm thinking right now to try and stuff all the data from the additionally needed columns into the SearchKeyword field (during the data sync from the ERP) and, worst case, add another column to the ac_Product table if the SearchKeyword is not big enough. That will break our rules of not modifying AC tables but might have to get done as last resort. As I said, any additional input is greatly appreciated.

Thanks again.

[heinscott]

Here is a method I used for searching across multiple columns (in one table) with FullText, using different weights per column. I think it should be easy enough to modify this to include the tables and columns you are interested in searching against, since I had to use JOINs to tie the ranks back to the actual product.

Code: Select all

protected List<Product> PerformFullTextSearch(string[] words)
    {
        List<Product> myresults = new List<Product>();
        string phrases = "";
        string original = "";
        for (int x = 0; x < words.Length - 1; x++)
        {
            original += words[x] + " ";
            string thisword = "";
            for (int y = 0; y < words.Length; y++)
            {
                if (y == 0 || y == (x + 1))
                    thisword += words[y];
                else
                    thisword += " " + words[y];
            }
            if (phrases.Equals(string.Empty))
                phrases += "\"" + thisword + "\"";
            else
                phrases += " AND \"" + thisword + "\"";
        }
        original += words[words.Length - 1];
        if (phrases.Equals(string.Empty))
            phrases = "\"" + original + "\"";
        else phrases += " AND \"" + original + "\"";
        StringBuilder selectQuery = new StringBuilder();
        selectQuery.Append("select TOP 30 ");
        selectQuery.Append("ProductId,RankTotal=isnull(RankName,0)+isnull(RankDescription,0)+isnull(RankSearchKeywords,0) ");
        selectQuery.Append("from ac_Products ");
        selectQuery.Append("left join (SELECT Rank * 50.0 as RankName, [KEY] from ");
        selectQuery.Append("FREETEXTTABLE(ac_Products, Name, '" + phrases + "')) as k ");
        selectQuery.Append("on k.[KEY]=ac_Products.ProductId ");
        selectQuery.Append("left join (select Rank * 1.0 as RankDescription, [KEY] from ");
        selectQuery.Append("FREETEXTTABLE(ac_Products, Description, '" + phrases + "')) as l ");
        selectQuery.Append("on l.[KEY]=ac_Products.ProductId ");
        selectQuery.Append("left join (select Rank * 10.0 as RankSearchKeywords, [KEY] from ");
        selectQuery.Append("FREETEXTTABLE(ac_Products, SearchKeywords, '" + phrases + "')) as m ");
        selectQuery.Append("on m.[KEY]=ac_Products.ProductId  ");
        selectQuery.Append("WHERE (RankName IS NOT NULL OR RankDescription IS NOT NULL OR RankSearchKeywords IS NOT NULL) AND VisibilityId = 0 ");
        selectQuery.Append("ORDER BY RankTotal DESC");
        CommerceBuilder.Data.Database database = Token.Instance.Database;
        System.Data.Common.DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());

        //EXECUTE THE COMMAND
        using (IDataReader dr = database.ExecuteReader(selectCommand))
        {
            while (dr.Read())
            {
                int pid = NullableData.GetInt32(dr, 0);
                Product myproduct = ProductDataSource.Load(pid);
                if (myproduct != null)
                    myresults.Add(myproduct);
            }
            dr.Close();
            dr.Dispose();
        }
        return myresults;
    }

Hope this helps.

Scott

[kstevenson]

Thanks, Scott! I'll have our developers look at it and see if they can make it work with the configurable column setup requirement.