All geek questions in one place

The lack of products in all categories and parent categories contains the keyword

I try to get all the categories and their number (there are no products in this category) of those products where the keyword is matched. The query I tried does not give me the correct result. I also want the parent categories to level 1 and their score as well.

eg. I try to look at keywords, then the category "hours" should be there with some quantity. The category “accessories” of the parent category is also calculated with the sum of its child categories.

my table structures:

tblProducts: There are 5 product categories: fldCategoryId1, fldCategoryId2, fldCategoryId3, fldCategoryId4 and fldCategoryId5. fldProductStatus must be "A"

+-----------------------------+-------------------+
| Field                       | Type              |
+-----------------------------+-------------------+
| fldUniqueId                 | bigint(20)        |
| fldCategoryId1              | bigint(20)        |
| fldCategoryId2              | bigint(20)        |
| fldCategoryId3              | bigint(20)        |
| fldCategoryId4              | bigint(20)        |
| fldCategoryId5              | bigint(20)        |
| fldProductStatus            | enum('A','P','D') |
| fldForSearch                | longtext          |
+-----------------------------+-------------------+

tblCategory:

+------------------------------+-----------------------+
| Field                        | Type                  |
+------------------------------+-----------------------+
| fldCategoryId                | bigint(20)            |
| fldCategoryName              | varchar(128)          |
| fldCategoryParent            | int(11)               |
| fldCategoryLevel             | enum('0','1','2','3') |
| fldCategoryActive            | enum('Y','N')         |
+------------------------------+-----------------------+

:

SELECT count( c.fldCategoryId ) AS cnt, c.fldCategoryLevel, c.fldCategoryParent, c.fldCategoryId, c.fldCategoryName, p.fldForSearch, c.fldCategoryParent
FROM tblCategory c, tblProducts p
WHERE (
    c.fldCategoryId = p.fldCategoryId1
    OR c.fldCategoryId = p.fldCategoryId2
    OR c.fldCategoryId = p.fldCategoryId3
    OR c.fldCategoryId = p.fldCategoryId4
    OR c.fldCategoryId = p.fldCategoryId5
)
AND p.fldProductStatus = 'A'
AND (
    MATCH ( p.fldForSearch )
    AGAINST (
        '+(watches watch)'
        IN BOOLEAN MODE
    )
)
GROUP BY c.fldCategoryId

. InnoDB FULLTEXT "fldForSearch".

EDIT: sqlfiddle

+6
3

, :

, 1 .

( 0 ) :

SELECT 
    c.fldCategoryId, 
    c.fldCategoryLevel, 
    c.fldCategoryName, 
    COUNT( * ) AS cnt
FROM tblCategory c
    LEFT JOIN tblProducts p ON
            (c.fldCategoryId = p.fldCategoryId1
        OR  c.fldCategoryId = p.fldCategoryId2
        OR  c.fldCategoryId = p.fldCategoryId3
        OR  c.fldCategoryId = p.fldCategoryId4
        OR  c.fldCategoryId = p.fldCategoryId5)
        AND p.fldProductStatus = 'A'
        AND MATCH ( p.fldForSearch )
            AGAINST (
                '+(watches watch)'
                IN BOOLEAN MODE
            )
GROUP BY 
    c.fldCategoryId
    c.fldCategoryLevel,  
    c.fldCategoryName
WITH ROLLUP;

:

  • p.fldForSearch, . fldForSearch ,
  • , 0 , . , , LEFT
  • MATCH, , .
+2

, (fldCategoryId...) . .

, , , OR.

, .

+1

(4 ), 

SELECT c1.fldCategoryId AS descendantId, c.fldCategoryId AS ancestorId
FROM tblcategory c1
LEFT JOIN tblcategory c2 ON c2.fldCategoryId = c1.fldCategoryParent
LEFT JOIN tblcategory c3 ON c3.fldCategoryId = c2.fldCategoryParent
JOIN tblcategory c ON c.fldCategoryId IN (
    c1.fldCategoryId,
    c1.fldCategoryParent,
    c2.fldCategoryParent,
    c3.fldCategoryParent
)


| descendantId | ancestorId |
|--------------|------------|
|            1 |          1 |
|            2 |          1 |
|            2 |          2 |
|          ... |        ... |
|            5 |          1 |
|            5 |          2 |
|            5 |          5 |
|          ... |        ... |

( ), descendantId ancestorId. , X X ( X). : 5 - 2 - 2 - 1. , 5 5, 2 1.

:

SELECT c.*, coalesce(sub.cnt, 0) as cnt
FROM tblCategory c
LEFT JOIN (
    SELECT tc.ancestorId, COUNT(DISTINCT p.fldUniqueId) AS cnt
    FROM tblProducts p
    JOIN (
        SELECT c1.fldCategoryId AS descendantId, c.fldCategoryId AS ancestorId
        FROM tblcategory c1
        LEFT JOIN tblcategory c2 ON c2.fldCategoryId = c1.fldCategoryParent
        LEFT JOIN tblcategory c3 ON c3.fldCategoryId = c2.fldCategoryParent
        JOIN tblcategory c ON c.fldCategoryId IN (
            c1.fldCategoryId,
            c1.fldCategoryParent,
            c2.fldCategoryParent,
            c3.fldCategoryParent
        )
    ) tc ON tc.descendantId IN (
        p.fldCategoryId1,
        p.fldCategoryId2,
        p.fldCategoryId3,
        p.fldCategoryId4,
        p.fldCategoryId5
    )
    WHERE p.fldProductStatus = 'A'
        AND MATCH ( p.fldForSearch )
            AGAINST ( '+(watches watch)' IN BOOLEAN MODE )
    GROUP BY tc.ancestorId
) sub ON c.fldCategoryId = sub.ancestorId

( , ):

| fldCategoryId | fldCategoryName | fldCategoryParent | fldCategoryActive | cnt |
|---------------|-----------------|-------------------|-------------------|-----|
|             1 |             Men |                 0 |                 Y |   5 |
|             2 |     Accessories |                 1 |                 Y |   5 |
|             3 |       Men Watch |                 1 |                 Y |   3 |
|             5 |           Watch |                 2 |                 Y |   5 |
|             6 |           Clock |                 2 |                 Y |   3 |
|             7 |     Wrist watch |                 1 |                 Y |   2 |
|             8 |           Watch |                 2 |                 Y |   4 |
|             9 |          watch2 |                 3 |                 Y |   2 |
|            10 |        fastrack |                 8 |                 Y |   3 |
|            11 |           swish |                 8 |                 Y |   2 |
|            12 |         digital |                 5 |                 Y |   2 |
|            13 |          analog |                 5 |                 Y |   2 |
|            14 |            dual |                 5 |                 Y |   1 |

:

, ( ) . MySQL .

There are other ways to optimize performance. One of them is to store the transitive closure table in an indexed temporary table. You can also save it in a regular table if the categories rarely change. You can also control it with triggers.

+1
source

Source: https://habr.com/ru/post/1016517/

More articles:


All Articles