Facebook Cookbook - Jay Goldman [114]
Concatenates strings together
substr(string, start, length)
Returns the specified substring
strpos(haystack, needle)
Returns the position of the needle in the haystack
lower(string)
Converts the string to lowercase
upper(string)
Converts the string to uppercase
The standard set of SQL-like operators are supported: =, >=, <, etc., parentheses for order of operations, and the arithmetic operators +, -, *, and /. FQL also supports the AND, OR, and NOT logical operators inside of queries.
Discussion
You can use the functions directly inline with your FQL queries:
SELECT upper(concat(first_name, " ", substr(last_name, 0, 1),
".")), birthday FROM user WHERE sex = "female" AND uid IN (SELECT uid2
FROM friend WHERE uid1 = $user) LIMIT 5;
That may not be the most practical query ever constructed, but it will return user’s first five female friends with their first name and last name’s first initial in all caps. Do with it what you will.
Indexed Facebook Tables and Fields
Problem
Which Facebook tables can I run queries against?
Solution
As of this writing, Facebook maintains a list of 17 tables you can query against. The Platform Wiki is a great source of information about which columns are contained in which table, so you should check there for information on building your own queries (see http://wiki.developers.facebook.com/index.php/FQL_Tables).
NOTE
For a quick overview of database indexing, see the Discussion in FQL Query Structure.
The list of available tables, along with the indexed columns you can use in WHERE clauses, includes:
album
Storage for albums created in the Facebook Photos app. You can query on:
aid (album ID of the album you’re looking for)
cover_pid (photo ID of the photo used on the cover)
owner (ID of the owner whose albums you’re looking for)
See Album Table for more information.
cookies
Browser cookies that your application might have dropped. This will only return cookies for your app, and there’s no way to request cookies for a different app for Facebook in general. You can query on:
uid (user ID associated with the cookie)
See Cookie Table for more information.
metrics
Metrics for your application. These are limited to your app, and there’s no way to request metrics for a different app. You can query on:
end_time (end date and time that metrics were collected, in epoch seconds). Note that the query is indexable (and therefore allowed) only if the date range of results is bounded and less than 30 days. See Formatting Relative Time for more on epoch seconds.
period (period, in seconds, for which you want data returned). Allowable values are one day (86400 seconds), one week (604800 seconds), or one month (2592000 seconds).
See Metrics Table for more information.
event
Storage for the events created in the Facebook Events app. You can query on:
eid (event ID of the event you’re looking for)
See Event Table for more information.
event member
Relationship table storing a specific user’s status for a specific event. Check this table to find out a user’s RSVP response for an event. You can query on:
uid (user ID of the user whose events you’re looking for)
eid (event ID of the event whose users you’re looking for)
See Event Member Table for more information.
friend
Relationship table storing friendships between users. You can query on:
uid1 (one of the two user IDs of the users you’re checking)
uid2 (the other user ID)
See Friend Table for more information.
friend request
Storage of pending friend requests for the current loggedinuser. Check this table to find out who has invited the current user to be friends. Note that you can’t query for anyone other than the current user and that the table contains only pending requests, not a history of all requests. You can query on:
uid_to (user ID of the friend receiving the requests; can only be the loggedinuser)
See Friend Request Table for more information.
friendlist
Storage of friend lists owned by