All geek questions in one place

Select photos by multiple tags

I have three MySQL tables — photos, tags, and tagsphotos — and the m: n relationship between photos and tags.

Photos: id | filename | ... Tags: id | name Tagsphotos: photo | tag

I want to select all photos with this condition:

 (tagged as "dirty" AND tagged as "road") AND (tagged as "light.front" OR tagged as "light.side") AND (tagged as "perspective.two-point")

... this means that I want to find all pictures with a dirty road in a two-point perspective and with side or front lighting.

How can i do this? Thanks.

+6
source share
3 answers

I think you'll have to join the tag table to the photo table four times ... pretty ugly.

 SELECT Photos.* FROM Photos JOIN ( Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) ) t1 ON (t1.photo = Photos.id) JOIN ( Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) ) t2 ON (t2.photo = Photos.id) JOIN ( Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) ) t3 ON (t3.photo = Photos.id) JOIN ( Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) ) t4 ON (t4.photo = Photos.id) WHERE (t1.name = 'dirty' AND t2.name = 'road') AND (t3.name = 'light.front' OR t3.name = 'light.side') AND (t4.name = 'perspective.two-point')

Subqueries are likely to be faster:

 SELECT * FROM Photos WHERE Photos.id IN ( SELECT Tagspohotos.photo FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) WHERE Tags.name = 'dirty' ) AND Photos.id IN ( SELECT Tagspohotos.photo FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) WHERE Tags.name = 'road' ) AND Photos.id IN ( SELECT Tagspohotos.photo FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) WHERE Tags.name = 'light.front' OR Tags.name = 'light.side' ) AND Photos.id IN ( SELECT Tagspohotos.photo FROM Tagsphotos JOIN Tags ON (Tags.id = Tagsphotos.tag) WHERE Tags.name = 'perspective.two-point' )
+3
source

Sorry, I didn’t understand that you are using MySQL - the updated answer given below.

Assuming that each tag can only be specified once for each photo (i.e. the photo cannot be marked as dirty several times):

 SELECT P.id, P.[filename] FROM Photos P INNER JOIN Tagsphotos TP ON TP.photo = P.id INNER JOIN Tags T ON TP.tag = T.id INNER JOIN ( SELECT 'dirty' name, 10 [weight] UNION SELECT 'road' name, 10 [weight] UNION SELECT 'perspective.two-point' name, 10 [weight] UNION SELECT 'light.front' name, 1 [weight] UNION SELECT 'light.side' name, 1 [weight] ) R ON T.name = R.name GROUP BY P.id, P.[filename] HAVING SUM(R.[weight]) >= 31
+1
source

Assuming the photo and tag columns are identifiers:

 select p.* from Photos p, Tags t, Tagsphotos tp where p.id = tp.photo and t.id = tp.tag and t.tagged in ("dirty", "road", "perspective.two-point", "light.front") group by p.photo having count(distinct t.tagged) = 4 UNION select p.* from Photos p, Tags t, Tagsphotos tp where p.id = tp.photo and t.id = tp.tag and t.tagged in ("dirty", "road", "perspective.two-point", "light.side") group by p.photo having count(distinct t.tagged) = 4
0
source

Source: https://habr.com/ru/post/914526/

More articles:


All Articles