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.
