Facebook Cookbook - Jay Goldman [129]
Photo Table
Problem
What’s the schema for the photo table?
Solution
The photo table stores the photos that have been created in the Facebook Photos app. Its fields are listed in Table 8-16. Queries to this table will only return data the current user is allowed to see (i.e., you can’t request photos that the current loggedinuser can’t see). More information about this table, including an up-to-date listing of fields, can be found at http://wiki.developers.facebook.com/index.php/Photo_(FQL).
Table 8-16. photo table fields
Name
Type
Index
Description
pid
int
•
Photo ID of this photo.
aid
int
•
Album ID of this photo.
owner
int
User ID that this photo belongs to.
src_small
string
URL of the small picture for this photo (max width of 75 px and max height of 225 px). Might be empty if this field wasn’t set by the creator.
src_big
string
URL of the big picture for this group (max width or height of 604 px). Might be empty if this field wasn’t set by the creator.
src
string
URL of the picture for this photo (max width or height of 130 px). Might be empty if this field wasn’t set by the creator.
link
string
URL to view this photo on Facebook.
caption
string
Caption for this photo.
created
string
Date this photo was created in epoch seconds. See Formatting Relative Time for more about epoch seconds.
Note that only the fields marked as “Index” in this table can be used in an FQL query’s WHERE clause, but any of the fields can appear in the SELECT.
Discussion
If you’d rather use the API to access photos, try the Photos.get() method.
Photo Tag Table
Problem
What’s the schema for the photo_tag table?
Solution
The photo_tag table contains the relationship between photos and the users who have been tagged in them. Its fields are listed in Table 8-17. Queries to this table will only return data the current user is allowed to see (i.e., you can’t request photos that the current loggedinuser can’t see). More information about this table, including an up-to-date listing of fields, can be found at http://wiki.developers.facebook.com/index.php/Photo_tag_(FQL).
Table 8-17. photo_tag table fields
Name
Type
Index
Description
pid
int
•
Photo ID of this photo.
subject
int
•
User ID of the person tagged in this photo.
text
string
The text entered for this tag.
xcoord
float
The x coordinate of the center of the tag square.
ycoord
float
The y coordinate of the center of the tag square.
created
int
The date this tag was created, in epoch seconds. See Formatting Relative Time for more about epoch seconds.
Note that only the fields marked as “Index” in this table can be used in an FQL query’s WHERE clause, but any of the fields can appear in the SELECT.
Discussion
If you’d rather use the API to access photo_tags, try the Photos.get() and Photos.getTags() method.
Retrieving the 10 Most Recent Photos from a User
Problem
I need to retrieve the 10 most recently posted photos by a specific user using FQL.
Solution
SELECT pid, src, caption FROM photo WHERE aid IN (SELECT
aid FROM album WHERE owner = $user) ORDER BY created DESC LIMIT 10;
Discussion
The owner field isn’t indexed in the photo table, so we need to make a subquery into the album table to find photos owned by a specific user.
The ORDER BY and LIMIT clauses can be applied to any of the queries in this chapter to order and/or constrain the result set.
Retrieving All Photos a User Is Tagged In
Problem
I need to retrieve all of the photos that a specific user has been tagged in using FQL.
Solution
SELECT pid, src, caption FROM photo WHERE pid IN
(SELECT pid FROM photo_tag WHERE subject = $user);
Discussion
This will return an empty set if the current loggedinuser doesn’t have permission to see any of the photos or if user hasn’t been tagged in any.
User Table
Problem
What’s the schema for the user table?
Solution
The user table stores all the Facebook users. Its fields are listed in Table