I have three tables in my database relevant to this question: venues, venue_tags, and venue_tag_relations. When someone adds a tag to a venue, it adds an entry to venue_tag_relations, with one column being the venue's primary index ID, and the other column being the tag's primary index ID. I've so far been able to query them all and join them correctly using an INNER JOIN syntax like so:
SELECT * FROM `venues` INNER JOIN (`venue_tags` INNER JOIN `venue_tag_relations` ON `venue_tags`.`vt_id`=`venue_tag_relations`.`vtr_tag`) ON `venue_tag_relations`.`vtr_venue`=`venues`.`vid`"
The problem is when a venue has more than 1 tag, and I want to get all the tags. I can't just grab vt_name, since that would only refer to the first (or last?) tag that's joined onto the table. How do I structure the JOIN syntax so I can parse all tags linked to a particular venue?
I can't just grab vt_name, since that would only refer to the first (or last?) tag that's joined onto the table
Actually, you can just grab vt_name. You are using an unusual syntax for the join, which is probably what`s causing it to return only one row. It should return one row per matched tag, if you phrase the join as follows:
SELECT `venues`.*,`venue_tags`.`vt_name` FROM `venues`
INNER JOIN `venue_tag_relations` ON `venue_tag_relations`.`vtr_venue`=`venues`.`vid`
INNER JOIN `venue_tags` ON `venue_tags`.`vt_id`=`venue_tag_relations`.`vtr_tag`
You can use PHP to iterate over that and group the tags together, or you can use a GROUP condition in the query itself combined with the GROUP_CONCAT function.
SELECT `venues`.*,GROUP_CONCAT(`venue_tags`.`vt_name`) AS `tags` FROM `venues`
INNER JOIN `venue_tag_relations` ON `venue_tag_relations`.`vtr_venue`=`venues`.`vid`
INNER JOIN `venue_tags` ON `venue_tags`.`vt_id`=`venue_tag_relations`.`vtr_tag`
GROUP BY `venues`.`vid`
This will return all the tags in a column named 'tags' as a comma separated list. (I assumed `vid` is the primary key for the venues table)
... WHERE (tags='2' OR tags LIKE '2,%' OR tags LIKE '%,2,%' OR tags LIKE '%,2') AND (tags='30' OR tags LIKE '30,%' OR tags LIKE '%,30,%' OR tags LIKE '%,30')
, but it seems like the WHERE clause can only use existing columns. Regardless, I'm sure there's an easier way of doing that within the query.
You need to apply the WHERE clause on the original column, not the aggregate result (which, as you've found out, can be only be used in a HAVING clause)
Sorry, I thought that's what you meant. In that case you'd need to add a HAVING clause anyway, though it's always preferable to leave what is possible to the WHERE clause.
OK, so that would return all venues with tags 2 or 4, with more than 1 tag attached to that venue? Looks like that would return a venue that has tags 4 and 5, or 2 and *. Not quite what I wanted...I guess I'm just asking if there's a better way to write out
HAVING (tags='2' OR tags LIKE '2,%' OR tags LIKE '%,2,%' OR tags LIKE '%,2') AND (tags='30' OR tags LIKE '30,%' OR tags LIKE '%,30,%' OR tags LIKE '%,30')
maybe without using the GROUP_CONCAT, or without doing such a crazy conditional syntax for parsing the concatenated string.
I see... it's harder that I thought to manipulate this query without testing it. In that case, I guess you need to join twice against the tags table with a different tag in the ON clause each time. This will remove the possibility of using GROUP_CONCAT() though
SELECT `venues`.* FROM `venues`
INNER JOIN `venue_tag_relations` ON `venue_tag_relations`.`vtr_venue`=`venues`.`vid`
INNER JOIN `venue_tags` ON `venue_tags`.`vt_id`=`venue_tag_relations`.`vtr_tag` AND `venue_tags`.`vt_name`='2'
INNER JOIN `venue_tags` AS vt ON `vt`.`vt_id`=`venue_tag_relations`.`vtr_tag` AND `vt`.`vt_name`='4'
GROUP BY `venues`.`vid`
Maybe someone has a better idea, but I don't see another way to filter on multiple distinct values in a group.
Another option is to use string search like you tried, but it's probably inefficient -
SELECT `venues`.*,GROUP_CONCAT(`venue_tags`.`vt_name`) AS `tags` FROM `venues`
INNER JOIN `venue_tag_relations` ON `venue_tag_relations`.`vtr_venue`=`venues`.`vid`
INNER JOIN `venue_tags` ON `venue_tags`.`vt_id`=`venue_tag_relations`.`vtr_tag`
GROUP BY `venues`.`vid`
HAVING LOCATE('2',tags) != 0 AND LOCATE('4',tags) != 0