How can I create a smart collection that checks for only *one* given keyword in Lightroom 3?

Question

I think I know my Lightroom pretty well, but I couldn't find a way to create a smart collection that returns photos containing only one given keyword. Just one. Say, all photos containing only the 'foo' keyword, no other keywords.

The only solution I found was:

1. create a smart collection that returns photos containing that given keyword 'foo',
2. select all photos and add them to another collection (say 'temp') for backup,
3. remove the given keyword 'foo' from the photos,
4. create a smart collection that returns the photos containing no keywords: this should return all the photos that only had the 'foo' keyword before I removed it in the previous step. Unfortunately all photos that had no keywords before step 3 will be returned as well, but that's OK, I don't have such photos.
5. do something with the photos returned by step 4, then go to the 'temp' collection and add the 'foo' keyword back.

Tedious.

Answer 1

@gerikson:

I'm sorry, I don't have an answer, I just want to say I wish more tools accepted real SQL as a query language ;)

But technically you can do this. The Lightroom catalog is an SQLite database -- try browsing it with Firefox's SQLite Manager addon, and you'll see it's pretty simple.

You could do this in Lua by writing an extension -- alas, I haven't gone through that step so I'm not sure how to do it.

If you don't need it to be an auto-updating collection the way smart collections work, but rather can deal with running a script every now and then, you could write an external tool to query the SQLite database the way you want, and create a smart collection xml file explicitly including the particular photos in question, and import that into Lightroom. (I needed to do this last bit myself -- see this question)

---

I just tried this select query and it works great:

 SELECT 
    img.id_local AS imageID, 
    kwdef.name AS keyword,
    img.rootFile AS fileID,
    files.originalFilename 
 FROM AgLibraryKeywordImage kwimg
 JOIN Adobe_images img
    ON img.id_local = kwimg.image
 JOIN AgLibraryFile files 
    ON img.rootFile = files.id_local
 JOIN AgLibraryKeyword kwdef
    ON kwdef.id_local = kwimg.tag
 GROUP BY kwimg.image
    HAVING COUNT(*)=1
      AND kwdef.name = ?

where you should either replace the ? with a keyword name (e.g. 'foo') or execute as a prepared statement. The GROUP BY clause does the magic: this groups the results by image, HAVING COUNT(*)=1 tells SQL to limit its output to images having 1 record.

This query probably won't work quite right for images that have stacks, in which case it needs to be modified to ensure all the JOINs are one-to-one (except for the keyword-image table)

Answer 2

I tried this, in LR 4: keyword contains "mykeyword" AND keyword doesn't contain "abcdfghijklnpqstuvxz"

where the 2nd string is all the letters NOT in "mykeyword"

not perfect, but it's a quick and dirty hack that mostly works.

Answer 3

all. I've found a way to do this. Not elegant, but it works quite well.

First, go to All Photographs in your catalog and select all of your photos. In the right pane, under Keywording, you will see a text box below Keyword Tags with ALL keywords found (most followed by an asterisk). Copy all of this text and paste it into a text editor.

Now, remove all of the asterisks (I use find/replace to simplify this), as well as the keyword(s) you wish to limit your search to. Once finished, copy all remaining text.

Now, create a smart collection. Add a rule for "Keyword" "Contains" "Single or Multiple Keyword(s) you want to search".

Next, add a rule for "Keyword" "Does not contain" and paste the edited text. Voila. Adobe really should have thought this one through, but it is Adobe...

Answer 4

In theory you should be able to do this with the "starts with" and "ends with" filters. Basically build it as:

matches ALL  
keyword  starts with  YourOneKeyWord  
keyword  ends with    YourOneKeyWord

But that doesn't seem to be working. :(