Nop 2.0 full text implementation

Hi all,

I am in process of looking how to retire the LIKE %% search that currently in nop in favor of SQL Server full text search. The motivation is that search is very important in any store so we need to make it easy for customer to find what they want to buy if we want them to buy it. In Nop 1.8/1.9 it was pretty easy case we had all search logic in DB in Stored Procedure. Now it's a bit more uphill :/

This is a piece of code that is currently responsible for search in NOP2:

            ////searching by keyword
            if (!String.IsNullOrWhiteSpace(keywords))
            {
                query = from p in query
                        //join pv in _productVariantRepository.Table on p.Id equals pv.ProductId into p_pv
                        //from pv in p_pv.DefaultIfEmpty()
                        from pv in p.ProductVariants.DefaultIfEmpty()
                        where (p.Name.Contains(keywords)) ||
                        (searchDescriptions && p.ShortDescription.Contains(keywords)) ||
                        (searchDescriptions && p.FullDescription.Contains(keywords)) ||
                        (pv.Name.Contains(keywords)) ||
                        (searchDescriptions && pv.Description.Contains(keywords))
                        select p;
                //TODO search localized values
            }

I've googled around a bit and found this:

http://sqlblogcasts.com/blogs/simons/archive/2008/12/18/LINQ-to-SQL---Enabling-Fulltext-searching.aspx

It pretty much says we need to make table valued function in SQL and call it from LINQ.

Piece of cake .. but we're using EF4 Code First .. how do I map a SP to there? Here it says you can't:

http://stackoverflow.com/questions/4845246/does-entity-framework-code-first-support-stored-procedures

.. unless I have access to underlying objectcontext.

So here comes the big question - how do I get hold of object context in ProductService? The last time I asked a similar question Anrei said not to and had me write 8 classes in 5 places to get a row of data from db ;-)

Filip

I am still stuck!

How do I get hold of object context used by Nop Commerce?

Anyone?

Filip

Try the following steps:
1. Add Nop.Data and EntityFramework.dll as references to Nop.Service project.
2. Inject IDbContext into ProductService constructor (not NopObjectContext).
3. Cast it to NopObjectContext
4. Use context.Database.ExecuteSqlCommand(...)

HTH

Andrei,

Thanks - I've just figured this out myself :-)

Filip

PS. I am actually quite far on the full text - the first result is that for a straight keyword search using full text cut my search time from unacceptable 16s down to not so bad 4s ..

fkierzek wrote:
How many products do you have? What are your computer specs (CPU, RAM)?

Try it for yourself: [url]polskaksiegarniainternetowa.com[/url]

The store is a bit over 50.000 products. It's running on 2 CPU 2 GB RAM VPS from SoftSys hosted in Chicago, USA.

The main SQL is:

DECLARE @Keywords NVARCHAR(500);
SET @Keywords = '9788324714711'; --978-83-247-1471-1

select TOP 100 p.* from FREETEXTTABLE(Product, FullText, @keywords) fts
join Product p on p.Id = fts.[key]
join ProductVariant pv on p.Id = pv.ProductId
where pv.Published = 1 and pv.Deleted = 0 and p.Published = 1 and p.Deleted = 0
order by fts.[rank] desc

[FullText] is my own column added to Product table where I concatenate all the things I want to search on - author, title, isbn, ean, original title etc. etc. The columns is NVARCHAR(250). Searching using the default LIKE shipping with 2.0 took 16s to complete the query .. and it couldn't find anything half the time ..

Note - what you are testing is a simplistic hack not a 'proper' implementation - i completely ignore everything (prices, available dates etc) in the interest of finding products users are searching for and doing it fast.

Given the amount of time and research it took me to figure this one out and the usefullness of this to general community I think full text should be standard feature that could be enabled from admin panel on SQL instllations that support this ..

Filip

I would be great if you can publish this modification. BTW, I think it can be done as plugin. Here is a simplified step list:

1. Create new FullTextProductService(derived from ProductService) and override SearchProducts method
2. Add all required logic there. For example, based on some setting value, it can use full-search custom implementation or the default one (base.SearchProduct())
3. Register this new IProductService implementation in custom IDependencyRegistrar class (like it's done in Nop.Plugin.Tax.CountryStateZip plugin). Just ensure that its 'Order' property is set to 1 (so the new implementation is registered after the default one)
4. Create a new plugin class derived from BasePlugin. Override its 'Install' method and execute SQL script for adding appropriate full-text columns and stored procedures