views:

32

answers:

2

Hi everybody,

This problem is giving me a real headache.

I have two tables in my database that solves sorting of categories with the help of a design called "Closure table". The first table is called categories, and the other one is named categoriesPaths.

Categories is simply a language-based table:

id | name
----------------
0  | Category 1
1  | Category 2

CategoriesPaths looks like this:

parent | children | level
-----------------------
1     | 1         | 0
1     | 2         | 1
2     | 2         | 0

To delete a path together with it's eventual child nodes, I run the following query: ($top is the top parent node level the deletion will begin at)

DELETE d FROM CategoriesPaths children
JOIN CategoriesPaths a USING (children)
WHERE a.parent = $top;

What I want to do is change the query to also delete the representative paths in the categories table. Unfortunately I have not enough SQL knowledge to do this. I tried to to make a workaround with a foreach loop before the query, but that fails to delete, since the categoriestable is referenced to categoriesPaths... By that said, I am pretty much stuck.

If anyone wish to learn more about the closure table design, here's a reference to the slideshare in which I learned it from: http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

Thanks a lot!

+2  A: 

Well, I would take a different path.

  1. Create a temporary table with the id's of all the items to delete
  2. Delete CategoriesPaths from temporary table
  3. Delete Categories from temporary table
  4. Drop the temporary table.

Like this:

-- not tested
CREATE TABLE tmp_to_delete
AS SELECT children.children
FROM CategoriesPaths children
JOIN CategoriesPaths a USING (children)
WHERE a.parent = $top;

DELETE FROM CategoriesPaths WHERE children IN (SELECT children FROM tmp_to_delete)
DELETE FROM Categories WHERE id IN (SELECT children FROM tmp_to_delete)

DROP TABLE tmp_to_delete
Jhonny D. Cano -Leftware-
+1  A: 

Why not use a transaction? http://www.firstsql.com/tutor5.htm

Ben Dauphinee
I use transactions at the queries, but not shown in this example since I do not see how it could affect the outcome of the query? (Assuming that the query should me made in one stage)
Industrial
@Industrial With a transaction, you can eliminate all joins from your sql, instead, issuing a set of separate queries. It removes some of the complexity, and also guarantees that your database stays consistent by reverting changes if something fails.
Ben Dauphinee