Delicious style Tags table

------=_Part_24650_16382124.1221133265556
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit


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-tp19433010p 19433010.html
Sent from the Php - Database mailing list archive at Nabble.com.

------=_Part_24650_16382124.1221133265556--

Re: Delicious style Tags table

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 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-tp19433010p 19433010.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

Re: Delicious style Tags table

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

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

RE: Delicious style Tags table

Simcha Younger-2 wrote:
>
> If there is only one search term - "soup" your where statement would be:
> Where `name` like 'soup'
>
> But you need two matches, and the terms are "soup", "vegetarian". Try:
> Where GROUP_CONCAT('name') like 'soup'
> AND GROUP_CONCAT('name') like 'vegetarian'
> group by taggings.id
>

You might be onto something, couldn't get your suggestion to work out the
box but after looking at the docs

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.ht ml#function_group-concat

Came the following

SELECT GROUP_CONCAT(tags.name SEPARATOR ' ') as tags, `taggings`.* FROM
`tags
LEFT JOIN `taggings` ON (tags.id = taggings.tag_id) WHERE (tags.name IN
('vegetarian', 'soup')) GROUP BY taggings.taggable_id ORDER BY tags

Gives me two records but only one of them has both tags in the concat
column. I can then pretty much do a length check on the tags column and see
when its less than the original input

Wasn't aware of the DROUP_CONCAT function. Cheers for pointing it out.

--
View this message in context: http://www.nabble.com/Delicious-style-Tags-table-tp19433010p 19439195.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

RE: Delicious style Tags table

If there is only one search term - "soup" your where statement would be:
Where `name` like 'soup'

But you need two matches, and the terms are "soup", "vegetarian". Try:
Where GROUP_CONCAT('name') like 'soup'
AND GROUP_CONCAT('name') like 'vegetarian'
group by taggings.id

You probably awnt a LEFT JOIN instead of a

Simcha Younger

-----Original Message-----
From: Catharsis [mailto:chris.lock@thisisbd.com]
Sent: Thursday, September 11, 2008 1:41 PM
To: php-db@lists.php.net
Subject: [PHP-DB] Delicious style Tags table


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-tp19433010p 19433010.html
Sent from the Php - Database mailing list archive at Nabble.com.

No virus found in this incoming message.
Checked by AVG - http://www.avg.com
Version: 8.0.169 / Virus Database: 270.6.20/1666 - Release Date: 11/09/2008
07:03


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Delicious style Tags table

I did a little testing, and this should work better:

Select...
From...
group by taggings.id
HAVING GROUP_CONCAT('name') like 'soup'
AND GROUP_CONCAT('name') like 'vegetarian'


Simcha Younger

-----Original Message-----
From: Catharsis [mailto:chris.lock@thisisbd.com]
Sent: Thursday, September 11, 2008 6:44 PM
To: php-db@lists.php.net
Subject: RE: [PHP-DB] Delicious style Tags table




Simcha Younger-2 wrote:
>
> If there is only one search term - "soup" your where statement would be:
> Where `name` like 'soup'
>
> But you need two matches, and the terms are "soup", "vegetarian". Try:
> Where GROUP_CONCAT('name') like 'soup'
> AND GROUP_CONCAT('name') like 'vegetarian'
> group by taggings.id
>

You might be onto something, couldn't get your suggestion to work out the
box but after looking at the docs

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.ht ml#function_grou
p-concat



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Delicious style Tags table

> 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.

??? Don't understand...

>> 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
>
>

> 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

> 

RE: Delicious style Tags table

Simcha Younger-2 wrote:
>
>
> I did a little testing, and this should work better:
>
> Select...
> From...
> group by taggings.id
> HAVING GROUP_CONCAT('name') like 'soup'
> AND GROUP_CONCAT('name') like 'vegetarian'
>
>

Yep, that works also, just to clarify it needed %'s either side to work
properly, e.g.

SELECT GROUP_CONCAT( tags.name ) AS tags, `taggings` . *
FROM `tags`
LEFT JOIN `taggings` ON ( tags.id = taggings.tag_id )
GROUP BY taggings.taggable_id
HAVING GROUP_CONCAT( tags.name ) LIKE '%soup%'
AND GROUP_CONCAT( tags.name ) LIKE '%vegetarian%'

I'll do some testing when I get time on lots of tags and see which of the
methods (I now have 3) is fastest.

Thanks
--
View this message in context: http://www.nabble.com/Delicious-style-Tags-table-tp19433010p 19799558.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