کتگوری چند سطحی( n level categorY)

سلام و وقت بخیر

میخوام قسمت کتگوری سایتم رو مثل سایت دیوار طراحی کنم مثلا در سایت دیوار به این صورت هست

دسته بندی استخدام و کاریابی —> رایانه—>نرم افزار —> برنامه نویس

منم همچین چیزی رو نیاز دارم ولی حقیقتا نمیدونم چطوری باید پیاده سازی کنم برای تحلیل جداولش هم مشکل دارم اگر کسی راهنمایی کنه ممنون میشم.

درود وقت بخیر . تشکر از ترجمه فوق العاده . در کد اینسرت کاربر جدید بجای گرفقتن مقدار rgt باید از lft استفاده کرد وگرنه کار نمیکنه ! LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;

بسیار عالی ممنون از شما

برای مدیریت دیتا های سلسله مراتبی نظیر مثال شما چند روش وجود دارد

اگه بخوایم ما روی یک مثال کار کنیم بر فرض دیتای زیر را داریم :

توی این تصویر به ما دسته بندی های تو در تو را نشان میدهد.

روش اول (استفاده از parent در جدول)

جدول را می سازیم :

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

دیتا ها را وارد میکنیم :

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

و نتیجه (DB-FIDDLE ) :

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

در این روش هر سطر در جدول حاوی اشاره گر به والد خود است. بالاترین عنصر در مثال ما electronics هست به همین علت مقدار در ستون والد NULL هست. در این روش آسان هست که بدانیم به که FLASH زیر مجموعه mp3players و آن ها زیر مجموعه PORTABLE ELECTRONICS هستند در حالی که این روش برای استفاده در کلاینت ساده است ولی در استفاده از اون در SQL میتواند مشکل ساز باشد.

دریافت همه درخت در این روش:

در این روش با دستور JOIN در MySQL می توانیم به کل درخت دست پیدا کنیم:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

پیدا کردن اخرین قسمت های نمودار (قسمت های بدون فرزند) در این روش :

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

محدودیت های این روش :

• کوئری های دشوار برای رسیدن به نتیجه

• باید از وضعیت سطح بندی ها از قبل اطلاع داشته باشیم برای دریافت کل مسیر کامل نمودار.

  • نیاز به مراقب در هنگام حذف کردن که دسته ی که دارای فرزند هست پاک نشود.

اما بعضی از این محدودیت ها را می توان با استفاده از کد سمت سرور حل کرد.

روش دوم (مدل تو در تو)

در این روش ما می توانیم سلسله مراتب خودمون رو با روش جدید نگاه کنیم

بدون استفاده از خطوط در نمودارمون اما به صورت درون هم قرار گرفتن آن ها به این ترتیب، دسته بندی های الکترونیک ما را به تصویر کشیدیم :

توجه کنید که سلسله مراتب ما هنوز حفظ شده است که والدین فرزندان خود را پوشش می دهند.

این شکل از سلسله مراتب را ما در یک جدول به همراه مقادیری که اشغال کردن عنصر ها در چپ و راست ذخیره میکنیم.

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
 (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
 (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

SELECT * FROM nested_category ORDER BY category_id;

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

از lft به جای left و از rgt به جای right استفاده میکنیم زیرا این دو کلمه از قبل در mysql رزرو شده است

چگونه مقدار چپ و راست را تعیین کنیم ؟ شروع به شماره گذاری میکنیم از سمت چپ به سمت راست

می توانیم این را روی یک نمودار درختی ساده هم نشان بدیم :

دریافت همه درخت در این روش:

ما میتوانیم تمام درخت را در این روش با استفاده از self-join با توجه به این که مقدار lft همیشه بین مقادیر lft و rgt والد خود هست.

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+

بر خلاف روش قبل در این روش بدون اینکه به عمق درخت توجه کند کار میکند.

پیدا کردن اخرین قسمت های نمودار (قسمت های بدون فرزند) در این روش :

پیدا کردن آن ها از روش قبلی بسیار ساده تر است . اگه به نمودار توجه کنید متوجه میشین که قسمت های بدون فرزند مقادیر rgt با lft تنها 1 عدد تفاوت دارند. در نتیجه :

SELECT name
FROM nested_category
WHERE rgt = lft + 1;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

پیدا کردن مسیر یک فرزند :

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY parent.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

پیدا کردن عمق عناصر :

ما قبلا در مورد چگونگی نشان دادن کل درخت نگاه کرده ایم، اما اگر می خواهیم عمق هر گره را در درخت نشان دهیم، بهتر است بدانیم چگونه هر گره در سلسله مراتب قرار می گیرد؟

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+

با استفاده از تابع REPEAT می توانیم عمق را نشان دهیم

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

اضافه کردن عنصر جدید

ما متوجه شدیم که این نوع نمودار را چگونه کار میکند اما سوالی که پیش می آید این است که چگونه به آن عنصر اضافه کنیم.

اگر دوباره به نمودار نگاه کنیم :

گره جدید دارای مقادیر LFT و RGT از 10 و 11 می باشد و تمام گره ها به سمت راست آنها مقدار LFT و RGT را با دو برابر افزایش می دهند. سپس گره جدید را با مقادیر مناسب LFT و rgt اضافه خواهیم کرد.

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

می توانیم اضافه شدن رو بررسی کنیم :

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

حذف عناصر

مسیر عملیاتی که شما هنگام حذف یک عنصر انجام می دهید بستگی به موقعیت گره در سلسله مراتب دارد؛ حذف عنصر های بدون فرزند راحت تر از حذف عنصر های دارای فرزند است زیرا ما باید گره های فرزند را اداره کنیم.

برای حذف عناصر فرزند :

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

دوباره بررسی می کنیم آیا حذف اتفاق افتاده است یا خیر

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

با روش زیر میتوانیم عنصر و تمام فرزندانش را حذف کنیم :

    LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

و دوباره بررسی میکنیم :

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

موفق باشید.