4

I am implementing my own blogging system to better understand the object oriented side of PHP, mySQL, and jQuery AJAX. I have a many-to-many relationship between Post_T and Tag_T, thus including an associative entity PostTag_T. When creating a post, I added functions that would check if the tag already exists, and either add the new tag and mapping, or just add the mapping to the existing tag.

The mishap that I noticed is that when deleting a Post, the tag row in Tag_T still exists while the mapping in PostTag_T is removed (set to ON DELETE CASCADE). How can I go about deleting the tag, if it isn't being used by any other post? I have the post ID while deleting the Post.

Useful Logic
Using the post id from deletion, delete from tag_t where in posttag_t the pID = post id AND no other posts (pID) in posttag_t is using the same tag id.

delete from tag_t where tID = (select tID from posttag_t where pID='post id') AND..


For your sanity, here is a visual representation

Created Post row in Post_T:

mysql create post

Mapping between post and tag in PostTag_T:

mysql associative entity

Created Tag row in Tag_T:

mysql create tag

When Deleting: using AJAX POST with data that calls a php file in url (guessing this is where I might need to add a query that searches tables..)

mysql php manage posts

Ben Sewards
  • 2,571
  • 2
  • 25
  • 43
  • 1
    What engine do you use? You should use InnoDB, and check this http://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete to see how to set foreign keys to delete on cascade – Royal Bg Aug 29 '13 at 06:27
  • I am using InnoDB as the storage engine. Thanks for the link: I do have the cascades set up the same way as the best answer, but am not getting results that match my criteria. – Ben Sewards Aug 29 '13 at 06:36

2 Answers2

1
DELETE FROM `tag_t` WHERE 
`tID` IN (SELECT `tID` FROM `posttag_t` WHERE `pID` = 'post_id') AND 
`tID` NOT IN (SELECT `tID` FROM `posttag_t` WHERE `pID` != 'post_id')

The last part of above query check is there any post using this tag or not.

Moein Hosseini
  • 4,309
  • 15
  • 68
  • 106
  • Thanks I was looking for 'NOT IN' as the second part, so close. I'll let you know if this query checks out – Ben Sewards Aug 29 '13 at 06:38
  • No luck with the second part of the query. Tested in mysql work bench: the rows in Tag_T are still there after deleting the only post mapped to those tags. – Ben Sewards Aug 29 '13 at 06:57
1

When you delete a post due to ON DELETE CASCADE you no longer have rows in posttag_t table thus it's useless to try to match against it. That means that you left with only option to delete all tags that are not associated with any posts.

You can do that with a query

DELETE t
  FROM tag_t t
 WHERE NOT EXISTS
(
  SELECT *
    FROM posttag_t 
   WHERE tid = t.tid
);

Here is SQLFiddle demo

Now you can let a trigger to take care of that

CREATE TRIGGER gt_ad_posttag_t
AFTER DELETE ON post_t
FOR EACH ROW
  DELETE t
    FROM tag_t t
   WHERE NOT EXISTS
  (
    SELECT *
      FROM posttag_t 
     WHERE tid = t.tid
  );

Here is SQLFiddle demo

peterm
  • 91,357
  • 15
  • 148
  • 157
  • The fact that there is a SQL Fiddle blows my mind. Thanks for pointing out the flaw in my plan (I knew that cascade would remove mappings from posttag_t, but still went ahead!). The trigger works great, it's too bad there can't be a PHP solution. Also, can I add this trigger to the post_t table? – Ben Sewards Aug 29 '13 at 07:10
  • 1. A php solution is to issue a delete statement that I provided right after you deleted a post. 2. The trigger in my example is defined exactly on `post_t` table `AFTER DELETE ON ` **`post_t`** – peterm Aug 29 '13 at 07:36
  • Accepted as Best Answer for providing me with both a trigger and a php solution. – Ben Sewards Aug 29 '13 at 08:13