CODEWITHSUNDEEP
sqlmysql-5.7
Søren Pedersen
Søren Pedersen

Reputation: 846

Refactoring MySQL query

We came up with this query:

SELECT tn1.* FROM tree_node tn1
WHERE tn1.type != "folder" AND tn1.parent IN (
    SELECT tn.id FROM tree_node tn
    LEFT JOIN tree_node_access tna ON tna.tree_node_id = tn.id
    WHERE (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))
) OR tn1.id IN (
    SELECT tn.id FROM tree_node tn
    LEFT JOIN tree_node_access tna ON tna.tree_node_id = tn.id
    WHERE (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))
)

But it must be possible to not have to repeat the subquery twice, as that query is exactly the same.

Is there a way to refactor this query?

Upvotes: 0

Views: 151

Answers (2)

The Impaler
The Impaler

Reputation: 48780

You don't say which MySQL version you are using. In MySQL 8.x you can use a CTE (Common Table Expression) for it.

For example:

with
a as (
  SELECT tn.id FROM tree_node tn
  LEFT JOIN tree_node_access tna ON tna.tree_node_id = tn.id
  WHERE (tn.project_id = 50 AND tna.access = 0 
    AND (tna.user_id = 8 OR tna.user_group_id IN (26)))
)
SELECT tn1.* FROM tree_node tn1
WHERE tn1.type != "folder" AND tn1.parent IN (select id from a)
   OR tn1.id IN (select id from a)

Upvotes: 1

PeterHe
PeterHe

Reputation: 2766

You can replace the or with UNION and replace the subqueries with JOINs:

SELECT tn1.*
FROM tree_node tn1
INNER JOIN tree_node tn
ON tn1.parent=tn.id
LEFT JOIN tree_node_access tna
ON tna.tree_node_id = tn.id
WHERE tn1.type != 'folder'
AND (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))
UNION
SELECT tn1.*
FROM tree_node tn1
INNER JOIN tree_node tn
ON tn.id=tn1.id
LEFT JOIN tree_node_access tna
ON tna.tree_node_id = tn.id
WHERE (tn.project_id = 50 AND tna.access = 0 AND (tna.user_id = 8 OR tna.user_group_id IN (26)))

Upvotes: 1

Related Questions