Your 'taggings' table is basically a link table to deal with a 'many
to many' relationship - i guess you know:
You have a table A and a table B that have a many-to-many
relationship, which is stored in table L. What query fetches all rows
in A that relate to all members of the set [B1, B2, ..., Bn]?
The problem here is that one entry in B relates to x entries in A. If
you want to know which entries in A relate to y entries in B, you're
creating a matrix with y dimensions: this means you'll have to join
the link table on itself the same amount of times as the matrix has
dimensions, resulting in the same amount of virtual tables that your
RDBMS will put in memory - iow, not safe.
You can manage to do this without the joins, I think, by using a
nested query and the HAVING keyword in your SQL. Selecting all ids of
entries that apply to all tags from a linktable would go something
like this:
SELECT link.entry_id, COUNT(link.entry_id) AS amount_of_links FROM
link WHERE link.tag_id IN (SELECT id FROM tag WHERE tag.name='soup' OR
tag.name='vegetarian') GROUP BY link.entry_id HAVING amount_of_links
>= 2
This is not an elegant solution and rather expensive! There can be
another one that I can't think of - i'd love to know it. I think your
best options are to either revise your design, or to consider whether
you really need a query like this - as far as i know they're not used
on flickr etc: these sites show tags for one entry or entries for one
tag.
Evert
On Thu, Sep 11, 2008 at 1:41 PM, Catharsis <chris.lock@xxxxxxxxxxxx> wrote:
>
> 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.
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
