Page 1 of 1

Complex Query Help

Posted: Fri May 07, 2010 7:53 pm
by jraede
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:

Code: Select all

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?

Thanks.

Re: Complex Query Help

Posted: Fri May 07, 2010 9:18 pm
by Eran
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:

Code: Select all

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.

Code: Select all

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)

Re: Complex Query Help

Posted: Fri May 07, 2010 10:33 pm
by jraede
Cool, that GROUP_CONCAT syntax is useful. Say that I want to only select venues that have attached tags with ID 4 AND 2. How would I do that?

EDIT: If I could use `tags` in the WHERE clause, I would try something like:

Code: Select all

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

Re: Complex Query Help

Posted: Sat May 08, 2010 2:19 am
by jraede
Figured it by using HAVING with the above after the GROUP BY statement, instead of WHERE.

But I still wonder, is there an easier way of doing it than what I wrote above?

Re: Complex Query Help

Posted: Sat May 08, 2010 6:47 am
by Eran
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)

Code: Select all

WHERE `venue_tags`.`vt_name` IN ('2','4')

Re: Complex Query Help

Posted: Sat May 08, 2010 2:09 pm
by jraede
Wouldn't that return true if vt_name is either 2 OR 4? I'm trying to get it to return true only if it's in 2 AND 4.

BTW, thanks for your help so far. Really appreciate it.

Re: Complex Query Help

Posted: Sat May 08, 2010 3:39 pm
by Eran
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.

Code: Select all

WHERE `venue_tags`.`vt_name` IN ('2','4') 
...
HAVING COUNT(DISTINCT `vt_name`) > 1

Re: Complex Query Help

Posted: Sat May 08, 2010 3:46 pm
by jraede
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

Code: Select all

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.

Re: Complex Query Help

Posted: Sat May 08, 2010 4:25 pm
by Eran
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

Code: Select all

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 -

Code: Select all

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
Or something similar