It is necessary to create a sql query , with which you can find out whether the user is in the partners' branch.

I explain: suppose there is a user A who has partners B, C, D, these partners have their partners and so on. The result is a tree.

For example: there is such a branch of partners: A -> C -> F -> H -> P , you need to know if user H is in a branch where one of the predecessors is user C.

The maximum level of entry to search in the branch = 10, but perhaps it will be more or less.

visual representation of the partner table

Description of table fields:

  • id - partner's record ID
  • user_id - user ID
  • partner_id - user partner ID
 CREATE TABLE IF NOT EXISTS `partners` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `partner_id` int(10) unsigned NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `partners_user_id_partner_id_unique` (`user_id`,`partner_id`), KEY `partners_user_id_partner_id_index` (`user_id`,`partner_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

PS Mysql v5.5

  • I correctly understood that a partner needs to look only up the tree? - etki
  • @etki Yes, they understood correctly. - ragmon
  • On the current storage scheme one request is indispensable. Rather, given the limited maximum nesting, it is possible, but it will be a monster, also very slow and gluttonous. You can change the storage scheme (go to any storage-oriented tree), or write a custom function that performs the required check. - Akina

1 answer 1

In this case, you can store the hierarchy in the string, as you wrote, through the separator, for each user. In this case, by defining the partner branch, you can compose an RLIKE or LIKE expression for the entry of a string value from the root branch. This is called the materialized path value ( MTZ ), and here is an example from a link:

 ID | NAME | PATH 100 | Electronics & Computers | /100 101 | Games | /100/101 102 | Xbox360 | /100/101/102 103 | PS4 | /100/101/103

Table queries will look like this:

 -- Найти детей игрушек (Games): SELECT * FROM catalog WHERE path LIKE “/100/101/%” -- Изменить родителя: UPDATE catalog SET path = REPLACE(path, '/100/101', '/200/201') WHERE path LIKE '/100/101/%'