How do i manage M:N Post-Tag relationship (eg. Edit/Delete)

Question

its when i starting trying implementing this that i got quite stuck.

some business rules

1 Post can have many Tags. 1 Tag can have many Posts

the database will look like.

• Posts (id, title, body, ...)
• Posts_Tags (post, tag)
• Tags (id, tag, ...)

when i insert - straightforward

• tags will come from user input as comma separated values
• explode($tags) to get individual tags
• foreach $tag
  • check if tag exists
  • if yes, get id
  • if no, insert tag & get id
• insert post with tags

i am wondering if this is the best way? can i do away with the loop to chk if the tags exists? or simplify it into 1 query?

update a post, abit harder

• how can i check if the user has updated any tags. another loop? but this time there will be some changes (italic)

• explode($tags) to get individual tags

• foreach $tag
  • check if this post has been tagged with this tag
  • if no,
    • does tag exists?
      • yes, get id
      • no, insert and get id
  • if yes, get id
• update post with tags

hmm, ... update is more confusing, how will u implement this?

i am using PHP 5.3, Zend Framework 1.10, Doctrine 2

Answer 1

An idea for update:

• Store the value for user JS enabled/disabled in a hidden value
• Tie an onchange event to the tags input then set a hidden variable to true so you know tags has changed
• If JS enabled and tags has changed delete all tags belongs to this post then insert the actual tags (like first)
  • Fallback: if JS disabled you need to check submitted tags against saved ones.

Answer 2

I do tags pretty similar to how your updated psuedocode is, except for one added step. You need to check to make sure no tags exist for the post that do not are not in the user input. For example if a post is tagged "php,sql,doctrine" and the user changes the tag to "c#,sql,doctrine" you have to know to delete the php tag as well.

So my code loops through existing tags to see which tags need to be removed, then I loop through the user supplied tags to see which need to be added.

Answer 3

I think the post-tags relationship should be many-to-many; ie: posts have and belong to many tags. This can be implemented with a junction table. Here's some literature on the subject. Then...

Insert: Do the same thing you are doing now, except you can fetch all tags in one query :

SELECT name FROM tags WHERE name IN ($tags)

Update: The easy way out: Remove all current tags, insert tags as if they were all new. Or...

$tags = explode(',' $_POST['tags']); // remove whitespaces

# delete all current tags that were not included in the edit
DELETE FROM post_tags WHERE post_id=$post_id AND tag_id NOT IN ($tags)

# find out what the current tags are, use a JOIN to find out their labels
SELECT tag_id FROM post_tags WHERE post_id=$post_id
fetch results, remove found tags from $tags, then
foreach new $tag, insert as if they were new

Answer 4

For your first question, you can simplify things into a single query. The (non-parameterized) SQL would look something like this:

SELECT * FROM Tags WHERE name='foo' OR name='bar' OR name='etc'
/* and so on and so forth */

You can then cycle through in your PHP code to check which tags were returned; I'd use key_exists("keyname", $associativeArrayContainingQueryResults).

I'd do something similar for the second part of your query, too. I have no idea what the performance benefit is, though. SQL might cache the results from several smaller queries better, but I don't know if it would be faster overall.

Answer 5

I think there is a Taggable behaviour in Doctrine. Have a look at this, this can help.