I have three MySQL tables - photos, tags and tagsphotos - and 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")
...which means that I want to find all pictures with dirty road, in two-point perspective and either with side or front light.
How can I do it? Thanks.
Today, you can tag people in photos when the photo is uploaded to a service like Facebook. But way before that tagging was available, you could use the Windows File Explorer to tag files. Nothing has changed in Windows 10.
Anytime you have more than one photo selected, you will add the same keywords to all those files. The only difference is that you will only see two tabs, and the Details pane will not show individual values for each image; it’ll say Multiple Values instead. To add the same keywords to more than one photo, select multiple files!
You can get to your File Explorer by clicking the shortcut in the middle of your dock (looks like a folder). Open your File Explorer by clicking the folder in your dock! To tag a file, select it, right click to view the options menu, select Properties, and when the box pops open, go to the Details tab.
To tag a file, select it, right click to view the options menu, select Properties, and when the box pops open, go to the Details tab. You can add as many tags as you want to the file by simply typing them in the text box next to Tags (separate them by colons). When done, click Apply and OK.
I think you're going to have to join the tags table to the photos 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 would probably 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'
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With