Complex Query Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Complex Query Help

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Complex Query Help

Post 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)
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Complex Query Help

Post 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.
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Complex Query Help

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Complex Query Help

Post 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')
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Complex Query Help

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Complex Query Help

Post 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
jraede
Forum Contributor
Posts: 254
Joined: Tue Feb 16, 2010 5:39 pm

Re: Complex Query Help

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Complex Query Help

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