调整查询以按关键字返回类别层次结构 [英] Adapt query to return category hierarchy by keyword

问题描述

我之前曾问过这个问题，然后有人建议说这是另一个先前回答过的问题的重复.但是，尽管经过3个小时的尝试，我仍无法使该解决方案适应我的需求.

I asked this question previously, then someone suggested that it was a duplicate of another previously answered question. However, I could not adapt that solution to what I need despite 3 hours of trying.

因此，我的新问题是如何使该解决方案适应我自己的需求.

So, my new question is how to adapt that solution to my own needs.

我的类别/子类别数据库架构的简化版本如下:

A simplified version of my category/subcategory database schema looks like this:

tblAllCategories

record_id  title                    level   parent_cat_id   parent_id   keywords
-------------------------------------------------------------------------------------------
1          Antiques & Collectables  0       NULL              NULL          junk
2          Art                      0       NULL              NULL          
25         Furniture                1       1                 1             
59         Office Furniture         2       1                 25            retro,shabby chic
101        Chairs                   3       1                 59            

注意:

• 0级=顶级类别，1级=二级，等等
• parent_cat_id是顶级类别(即级别0)
• parent_id是指紧随相关级别之上的级别

我添加了关键字列，以辅助关键字搜索，因此，如果用户输入了关键字但未选择要钻取的类别，则将返回某些相关类别的项目.

I added the keyword column to assist keyword searches so that items in certain relevant categories would be returned if the user entered a keyword but did not select a category to drill down into.

因此，在前端，在用户输入关键字(例如"Retro")之后，我不仅需要返回在其关键字栏中包含术语"retro"的类别，还需要返回所有更高级别的类别.因此，根据上面的模式，对复古"的搜索将返回类别59以及其超级类别-25和1.

So, at the front end, after the user enters keyword, e.g., "Retro", I need to return not only the category that has the term "retro" in its keyword column, but also all higher level categories. So, according to the schema above, a search on "retro" would return category 59 along with its super-categories - 25 and 1.

查询应按级别排序，以使前端搜索结果看起来像这样(经过必要的编码后):

The query should be sorted by level, such that the front end search results would look something like this (after necessary coding):

提供的解决方案来自此问题

查询如下:

SELECT T2.id, T2.title,T2.controller,T2.method,T2.url
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM menu WHERE id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 31, @l := 0) vars,
        menu m
    WHERE @r <> 0) T1
JOIN menu T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC;

我需要修改此查询以解决传递的关键字而不是ID.

I need to adapt this query to work off a passed keyword, not an ID.

推荐答案

编辑vars子查询，以使@r等于具有键的行的record_id，类似

Edit the vars subquery to have @r equal to the record_id of the row with the keywork, something like

SELECT T2.record_id, T2.title,T2.level,T2.keywords
FROM (SELECT @r AS _id
           , (SELECT @r := parent_id 
              FROM   tblAllCategories 
              WHERE record_id = _id) AS parent_id
           , @l := @l + 1 AS lvl
      FROM   (SELECT @r := record_id, @l := 0
              FROM   tblAllCategories
              WHERE  keywords like '%retro%') vars
           , tblAllCategories m
      WHERE  @r <> 0) T1
     JOIN tblAllCategories T2 ON T1._id = T2.record_id
ORDER BY T1.lvl DESC;

SQLFiddle演示

将键工作用逗号分隔值并不是最好的选择，此表与关键字表(带有强制连接表)之间的多对多关系会更好，因为它将避免使用LIKE.在此示例中，如果存在另一个带有关键字"retrobike"的类别，则该类别及其所有层次也将出现在结果中.

Having the keywork as a comma separated values is not the best, a many to many relationship between this table and a keyword table (with the compulsory junction table) will be better as it will avoid the use of LIKE. In this example if there were another category with the keyword 'retrobike' that category and all his hierarchy will also be in the result.

这篇关于调整查询以按关键字返回类别层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！