How to get photos or photos containing only one user with tags using either FQL or Graph API?

Question

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

I started with this FQL call:

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

From this, I get results containing images containing the tag of this user (in this case: me ()). However, these images may include more than one tagged user. I was hoping to get around this problem using the SQL HAVING clause along with COUNT (http://stackoverflow.com/a/3710501/1406986). Unfortunately, I do not think that FQL supports this function, because when I tried to make this call:

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

I got a bug from Facebook. Please correct me if FQL really supports this functionality.

In an attempt to solve this problem using the Graph API, I made the following request:

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

This returns the user's photos, but from now on I had to iterate over each photo in search of those that contain only one tag. I found this impractical if, for example, I need to find an image for each of the user's friends, in which there is only their friend and the others are not. To do this, I would need to restore all their photos of each other individually, and then sort through them all.

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

Answer 1

Well, I managed to create FQL for this problem, but it’s not 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 current user's photo_tags

 SELECT pid FROM photo_tag WHERE subject = me() 

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

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

I joined the same table again (yes 3 connects to one table), but this time I wanted photo_tags, where I was the subject And , which were not in the list that I got earlier (photos where I was the subject of someone ), so this query returns all photo_tags where the user is the only one tagged in this 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 request was just a union to get information from the table of photos in the photographs, I was the only one marked

 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()) ) )