MS SQL Server
  1. MS SQL Server
  3. Recherche par mot-key de hiérarchie CTE SQL Server
Intereting Posts
Je ne suis pas en mesure d'insert des données dans une database SQL Server Vous searchz des améliorations des performances du count SQL. SqlBulkCopy ne copy aucune donnée SQL Server: Est-il possible d'get une procédure ou une création de table et une date de dernière modification? Faire une requête comme treeview SQL détecter les modifications dans le journal stocké dans la database Configuration matérielle requirejse pour une petite application Silverlight, qui consum des données à partir d'un DataService WCF plutôt que d'exposer des données à partir d'une database SQLServer Existe-t-il un Entity Framework 7 Database-First POCO Generator? Déterminer le décalage de timezone dans T-SQL Calcul de plus de 24 heures dans SQL Server 2008 Lire la valeur des données du server sql toutes les x secondes d'asp.net Scénario de design de database SQL-Server DB (dissortingbué ou centralisé) Lecture d'un champ de bits (utilisé pour Selected Days) et détermination de valeurs à partir de celui-ci dans c # Obtenir des valeurs null de résultat à partir d'une requête Sélectionnez les loggings classés dont la sum correspond à une certaine valeur

Recherche par mot-key de hiérarchie CTE SQL Server

J'ai rencontré un problème épineux avec la search récursive dans une procédure stockée de la boutique en ligne. Fondamentalement, cette procédure unique returnnera tous les produits en tenant count des filters de base et de la pagination, et en utilisant une table de catégories parent / enfant pour effectuer des controls récursifs dans la hiérarchie. Cela fonctionne magnifiquement et les CTE sont extrêmement rapides, mais l'ajout récent d'une search par mot-key qui doit effectuer une search sur le nom de la catégorie, le nom du produit et le numéro de style a causé des drames.

Cela semblait assez sortingvial au début car le 1er CTE génère déjà une table de toutes les catégories pertinentes dans la hiérarchie basée sur le @categoryid fourni, puis se joint au rest des tables spécifiques au produit pour tout le filtrage. La search de nom de produit et de numéro de style fonctionne correctement, mais je ne peux pas faire fonctionner une search par nom de catégorie car elle doit searchr dans l'arborescence des catégories toutes les correspondances dans l'tree hiérarchique en commençant par le haut.

EDIT: Je pense maintenant qu'il peut être beaucoup plus facile d'append une table "tag" contre les produits qui stocke tous les mots-keys liés tels que le nom de la catégorie, le nom du produit et le style etc et search directement sur les tags.

Par exemple, un sous-set de la hiérarchie Catégorie ressemble à ceci: 

Mens - Polos - Jerseys - Pants Womens - Pants - Shirts - Polos Supporters - State Of Origin - Mens - Womens - Kids - Bulldogs - Jerserys - Pants - Shirts - Caps - Warratahs

Dans mon exemple de code ci-dessous je passe un terme de search "origine mens" qui devrait renvoyer tous les produits dans la catégorie "État d'origine" qui sont également dans la catégorie "Mens". La seule chose sur laquelle il correspond est les noms de produits qui commencent par "Origine" et rien d'autre parce que la catégorie au niveau du produit n'est pas "État d'origine" car c'est le parent. Toute aide ici serait fantastique! 

 -- Variable Declarations DECLARE @categoryid int DECLARE @minprice int DECLARE @maxprice int DECLARE @sizefilter int DECLARE @colourfilter int DECLARE @searchssortingng varchar(255) DECLARE @totalrows int -- Variables values for testing SET @categoryid = 0 SET @minprice = 0 SET @maxprice = 0 SET @sizefilter = 0 SET @colourfilter = 0 SET @searchssortingng = 'origin mens' -- Setup paging table DECLARE @indextable table (rownum int identity(1,1), recordid int); BEGIN -- First run CTE recursively over all categories in hierarchy ;WITH categoryCTE AS ( SELECT cat.id as CategoryId, cat.name as CategoryName FROM dbo.shopcategory AS cat WHERE (@categoryid = 0 OR cat.id = @categoryid) AND cat.isenabled = 1 UNION ALL SELECT child.id as CategoryId, child.name as CategoryName FROM dbo.ShopCategory AS child INNER JOIN categoryCTE AS parent ON child.parentid = parent.CategoryId WHERE child.isenabled = 1 ), -- Now join CTE onto products tables via linker product_shopcategory productsCTE AS ( SELECT p.id, ppc.shopcategoryid, ppc.listorder as catlistorder FROM categoryCTE as cat INNER JOIN product_shopcategory ppc ON ppc.shopcategoryid = cat.CategoryId INNER JOIN product p ON ppc.productid = p.id INNER JOIN productlocality pl ON pl.productid = p.id -- ** SEARCH - Join List to Table function of keywords INNER JOIN dbo.udf_parseList(@searchSsortingng, ' ') s ON (cat.CategoryName + p.Name + p.stylenumber LIKE '%' + s.array_Value + '%') LEFT JOIN product_quantity pq ON pq.productid = p.id AND pq.localityid = @localityid LEFT JOIN productcolour pc ON pc.productid = p.id LEFT JOIN productcolourswatch pcs ON pc.productcolourswatchid = pcs.id LEFT JOIN product_productsize pps ON pps.productid = p.id LEFT JOIN productsize ps ON pps.productsizeid = ps.id WHERE p.isenabled = 1 AND pq.quantity > 1 AND (pc.isenabled IS NULL OR pc.isenabled = 1) AND (@minprice = 0 OR pl.price >= @minprice) AND (@maxprice = 0 OR pl.price <= @maxprice) -- Colour Group Filters AND (@colourfilter = 0 OR (pcs.swatchgroupid = @colourfilter AND (pq.productcolourid = pc.id AND pq.quantity > 0)) ) -- Size Group Filters AND (@sizefilter = 0 OR (ps.sizegroupid = @sizefilter AND (pq.productsizeid = pps.productsizeid AND pq.quantity > 0)) ) ) -- Create Paging table of results and ssortingp out duplicates with group by INSERT INTO @indextable (recordid) SELECT DISTINCT id FROM productsCTE GROUP BY id ORDER BY id;

Finalement résolu! Je suis presque tombé sur la création d'une structure de table de balises complète afin que je puisse chercher directement sur des balises de mots-keys plutôt que sur des données directes, mais en essayant d'écrire une table de balises contenant une imbrication de la hiérarchie de catégories. assez facile.

Dans la procédure de résolution ci-dessous, j'ai créé une nouvelle colonne dans CategoryCTE pour contenir une list délimitée par des virgules de noms de catégories qui est construite de manière récursive et qui suit alors l'arborescence complète pour le CategoryId fourni. Maintenant que j'ai une list de noms de catégories délimitée par des virgules, je peux ensuite factoriser cela dans mon second CTE et exécuter une clause LIKE standard en prenant en count le nom du produit, le numéro de style et les noms de catégorie. Enfin, afin de rendre cette search plus intelligente, j'ai fait une search par mot-key incluant tous les mots-keys, de sorte que "origine masculine" renvoie uniquement les produits correspondant à ces mots-keys, et ceci à l'aide de la clause NOT EXISTS.

J'espère que cela aide quelqu'un d'autre, il fonctionne très vite aussi! 

 -- Variable Declarations DECLARE @categoryid int DECLARE @minprice int DECLARE @maxprice int DECLARE @sizefilter int DECLARE @colourfilter int DECLARE @searchssortingng varchar(255) DECLARE @totalrows int -- Variables values for testing SET @categoryid = 0 SET @minprice = 0 SET @maxprice = 0 SET @sizefilter = 0 SET @colourfilter = 0 SET @searchssortingng = 'origin mens' -- Setup paging table DECLARE @indextable table (rownum int identity(1,1), recordid int); BEGIN -- First run CTE recursively over all categories in hierarchy inclusive of supplied categoryId ;WITH categoryCTE AS ( SELECT cat.id as CategoryId, cat.name as CategoryName, CONVERT(varchar(255),cat.name) AS Tags FROM dbo.shopcategory AS cat WHERE (@categoryid = 0 OR cat.id = @categoryid) AND cat.isenabled = 1 UNION ALL SELECT child.id as CategoryId, child.name as CategoryName, CONVERT(varchar(255), parent.Tags + CONVERT(varchar(32),',' + child.name)) AS Tags FROM dbo.ShopCategory AS child INNER JOIN categoryCTE AS parent ON child.parentid = parent.CategoryId WHERE child.isenabled = 1 ), -- Now join CTE onto products tables via linker product_shopcategory productsCTE AS ( SELECT p.id, ppc.shopcategoryid, ppc.listorder as catlistorder FROM categoryCTE as cat INNER JOIN product_shopcategory ppc ON ppc.shopcategoryid = cat.CategoryId INNER JOIN product p ON ppc.productid = p.id INNER JOIN productlocality pl ON pl.productid = p.id LEFT JOIN product_quantity pq ON pq.productid = p.id AND pq.localityid = @localityid LEFT JOIN productcolour pc ON pc.productid = p.id LEFT JOIN productcolourswatch pcs ON pc.productcolourswatchid = pcs.id LEFT JOIN product_productsize pps ON pps.productid = p.id LEFT JOIN productsize ps ON pps.productsizeid = ps.id WHERE p.isenabled = 1 AND pq.quantity > 1 AND (pc.isenabled IS NULL OR pc.isenabled = 1) AND pl.localityid = @localityid AND (@minprice = 0 OR pl.price >= @minprice) AND (@maxprice = 0 OR pl.price <= @maxprice) -- Keyword Search filter AND (@searchssortingng = '' OR NOT EXISTS ( SELECT NULL FROM dbo.udf_parseList(@searchSsortingng, ' ') WHERE cat.Tags + p.Name + p.stylenumber + pc.stylenumber NOT LIKE '%' + array_Value + '%' ) ) -- Colour Group Filters AND (@colourfilter = 0 OR (pcs.swatchgroupid = @colourfilter AND (pq.productcolourid = pc.id AND pq.quantity > 0)) ) -- Size Group Filters AND (@sizefilter = 0 OR (ps.sizegroupid = @sizefilter AND (pq.productsizeid = pps.productsizeid AND pq.quantity > 0)) ) ) -- Create Paging table of results and ssortingp out duplicates with group by INSERT INTO @indextable (recordid) SELECT DISTINCT id FROM productsCTE GROUP BY id ORDER BY id;