SELECT `tags`.*, count(taggable_id) as cnt, `taggings`.* FROM `tags`
JOIN `taggings` ON tags.id = taggings.tag_id WHERE (name IN ('vegetarian',
'soup', 'lunch', 'curry')) group by taggable_id
Evert Lammerts-2 wrote:
>
> 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]?
>
Well I was planning on getting the tags back first because in theory table B
could be C or D or whatever. I handle that in code by processing all the
records I get back from the query to find out what table that are in.
Evert Lammerts-2 wrote:
>
> 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
>
You know this got me thinking and I came up with this
SELECT `tags`.*, count(taggable_id) as count, `taggings`.* FROM `tags`
JOIN `taggings` ON tags.id = taggings.tag_id WHERE (name IN ('vegetarian',
'soup')) group by taggable_id
Now that gives me ssomething I can work with 'count' should be the same
value as the number of tags supplied (2), but I couldn't figure out how to
add that into the where clause, of course I could use a subselect and just 1
will be fine, something like
<pre>
SELECT * from
(SELECT `tags`.name, count(taggable_id) as count, `taggings`.* FROM `tags`
JOIN `taggings` ON tags.id = taggings.tag_id WHERE (name IN ('vegetarian',
'soup')) group by taggable_id ) tbl1
WHERE cnt = 2
</pre>
Evert Lammerts-2 wrote:
>
> as far as i know they're not used on flickr etc: these sites show tags for
> one entry or entries for one
> tag.
>
Delicious definately does it, below is an example using my account, for
instance, all my music links
http://delicious.com/CatharsisJelly/music
All Music that appears on myspace
http://delicious.com/CatharsisJelly/music+myspace
All Music that appears on myspace that are metal
http://delicious.com/CatharsisJelly/music+myspace+metal
Your right about Flickr :)
Cheers for the suggestion, open to ideas still :) Questions welcome.
--
View this message in context: http://www.nabble.com/Delicious-style-Tags-table-tp19433010p19438021.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
