Delicious style Tags table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



So I am having difficulty thinking of how to make this select query.  I have
two tables that mimic tags similar to flickr, delicious etc.  They are
defined below



CREATE TABLE IF NOT EXISTS `taggings` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `tag_id` int(11) NOT NULL,
  `taggable_id` int(11) NOT NULL,
  `taggable_type` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `index_taggings_on_tag_id_and_taggable_id_and_taggable_type`
(`tag_id`,`taggable_id`,`taggable_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `tags` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `unique_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Just to explain the taggings table, tag_id points directly to the tag table,
taggable_id is the id of the item you have tagged in another table.  The
taggable_type is string reference to the table that the item you tagged sits
in, so in the exaplme below it would be a table called 'recipes'


So, say you have 2 items each with the same tag but one item as two tags. 
For instance a two soup Recipes could be tagged with 'soup' but only one of
them is vegetarian.  So Recipe 1 has the tag 'soup' and recipe 2 has 'soup'
and 'vegetarian'


I want to be able to pass my SQL the word 'soup' and it return both records
in taggings table which will point to both recipes.  However if I want just
vegetarian soups then I only want it to return the one record.  I hope that
is understandable


What I have currently (below) is just a simple join.  Which obviously
doesn't work.  I just cant think how to piece these two tables together to
get the records I want.

SELECT `tags`.*, `taggings`.* FROM `tags`
 JOIN `taggings` ON tags.id = taggings.tag_id WHERE (name IN ('vegetarian',
'soup'))




-- 
View this message in context: http://www.nabble.com/Delicious-style-Tags-table-tp19433010p19433010.html
Sent from the Php - Database mailing list archive at Nabble.com.

[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux