G
G
Grigory Vasilkov2017-01-07 11:06:25
MySQL
Grigory Vasilkov, 2017-01-07 11:06:25

How can you optimize the following SQL query - displaying an element by the nearest parent?

The request works correctly, but is a "request within a request", which is not very good.

SELECT
 t1.*
FROM (
 SELECT
   t1.`id` `user_id`
   , t1.`parent_id` `owner_id`
   , t1.`level`
   , t2.`product_id`
   , t2.`url`
 FROM
   `user_tree` t1
 INNER JOIN
   `product` t2 ON t2.`user_id` = t1.`parent_id`
 WHERE
   t1.`id` = 33 # (USER_ID HERE)
 ORDER BY
   t1.`level`
) t1
GROUP BY
 t1.`product_id`
ORDER BY
 t1.`product_id`

The tables look like this
user_tree
id -- parent_id -- level
product
id -- product_id -- user_id -- url
What behavior is needed:
There are users, they can invite each other - that is, a user tree
There are products, they belong to users Needed
for a user of any level nesting select products belonging to it, and if not, then to its parent, and if not, then to its parent, etc., but the total number of products should not be more than the number of unique product_id, thus "get products for the nearest parent" .

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question