How do you retrieve a photo or photos containing only one tagged user using either FQL or Graph API?

Question

I have tried accomplish this using both FQL and the Graph API.

I began by trying this FQL call:

$fql = "SELECT pic, src_big FROM photo WHERE pid IN (SELECT pid FROM photo_tag WHERE subject = me())";

From this I receive results that include images containing a tag of the given user (in this case: me()). However, these images may include more than one tagged user. I was hoping to work around this issue by using the SQL HAVING clause along with COUNT (http://stackoverflow.com/a/3710501/1406986). Unfortunately I don't think FQL supports this functionality because when I attempted this call:

$fql = "SELECT pic, src_big FROM photo WHERE pid IN (SELECT pid FROM photo_tag HAVING COUNT(subject) = 1";

I recived an error from Facebook. Please correct me if FQL does in fact support this functionality.

In attempt to tackle this using the Graph API I made the following request:

$userPics = $facebook->api('/me/photos');

This returns the user's photos, however, from this point I was forced to iterate over every photo looking for ones that only contain one tag. I found this to be impractical if, for instance, I need to find an image for each of a user's friends that only contains their friend and no others. To do this I would need to retrieve all of each of their friend's photos individually and then iterate over all of them.

I'm still searching for a good solution. Please post your ideas and solutions.

Answer 1

Well I have managed to create a FQL for this problem, but it's not pretty at all

SELECT object_id, pid, src_big FROM photo WHERE pid IN
  (SELECT pid FROM photo_tag 
    WHERE pid IN (SELECT pid FROM photo_tag WHERE subject = me()) 
    AND NOT (pid IN 
      (SELECT pid  FROM photo_tag WHERE pid IN 
        (SELECT pid FROM photo_tag WHERE subject = me()) 
      AND subject != me())
    )
  )

Explanation:

The first one is pretty simple, get the photo_tags of the current user

SELECT pid FROM photo_tag WHERE subject = me()

The next one I joined the same table, but this time I wanted the photos where I was with someone

SELECT pid  FROM photo_tag WHERE pid IN 
  (SELECT pid FROM photo_tag WHERE subject = me()) 
AND subject != me()

The I joined the same table again (yes 3 joins with same table), but this time I wanted the photo_tags where I was the subject AND that weren't on the list I got before (the photos where I was the subject with someone), so this query returns all the photo_tags where the user is the only one tagged in that photo

SELECT pid FROM photo_tag 
WHERE pid IN (SELECT pid FROM photo_tag WHERE subject = me()) 
   AND NOT (pid IN 
     (SELECT pid  FROM photo_tag WHERE pid IN 
        (SELECT pid FROM photo_tag WHERE subject = me()) 
      AND subject != me())
   )

The last addition to the query was just a join to get the info from the photo table of the photos were I was the only one tagged

SELECT object_id, pid, src_big FROM photo WHERE pid IN
  (SELECT pid FROM photo_tag 
    WHERE pid IN (SELECT pid FROM photo_tag WHERE subject = me()) 
    AND NOT (pid IN 
      (SELECT pid  FROM photo_tag WHERE pid IN 
        (SELECT pid FROM photo_tag WHERE subject = me()) 
      AND subject != me())
    )
  )