0

I hope this is not a silly question.

I have a table containg the products of an online store. I am using fulltext search in boolean mode.

People search for, let us say, Apple iPhone 11.

I don't want "Cleaning kit for Apple iPhone 11" to come first and the product itself "Apple iPhone 11" to come second.

1

You just need to link back to the Product table and sort by some other criteria on there. You can ask the Full Text engine to rank the results, but that really just ranks how closely the result matches their search, which is not what you are looking for. I have a sample here using MS SQL Server.

Since you didn't provide a schema, I put together something that would show what I mean.

DROP TABLE IF EXISTS dbo.Product

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1)
    , ProductName NVARCHAR(100) NOT NULL
    , SearchPrecedence INT NULL
    , CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID)
    )

GO

BEGIN TRY
    CREATE FULLTEXT CATALOG FTC_FullText AS DEFAULT
END TRY
BEGIN CATCH
    PRINT 'Full Text Catalog already exists.'
END CATCH

CREATE FULLTEXT INDEX ON dbo.Product(ProductName) KEY INDEX PK_Product;

GO

INSERT INTO dbo.Product 
(ProductName, SearchPrecedence)
VALUES ('Iphone', 1)
    , ('Iphone Clean', 100)

GO

/** NOTE: need to wait a bit here so that we give the engine time to build the full text results.  
    This code works, but Full Text is not synchronous with writes
    **/

WAITFOR DELAY '00:00:30.000'

DECLARE @Search NVARCHAR(100) = '"*Iphone*"'

SELECT P.ProductID
    , P.ProductName
    , P.SearchPrecedence
FROM dbo.Product AS P
WHERE P.ProductID IN (SELECT PFT.[Key] FROM CONTAINSTABLE(dbo.Product, *, @Search) AS PFT)
ORDER BY P.SearchPrecedence

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.