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@xxxxxxxxxxxx]
Sent: Thursday, September 11, 2008 1:41 PM
To: php-db@xxxxxxxxxxxxx
Subject: 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-tp19433010p19433010.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
