Facebook Cookbook - Jay Goldman [113]
FQL Query Structure
Problem
What’s the basic structure of an FQL query?
Solution
FQL mimics SQL directly here, so the basic structure is:
SELECT [fields] FROM [table] WHERE [conditions]
In addition, you can also make use of SQL-like ORDER BY and LIMIT clauses:
SELECT [fields] FROM [table] WHERE [conditions] ORDER BY
[field] LIMIT [offset], [rowcount]
Discussion
Here are a few noteworthy differences between SQL and FQL that might trip you up if you’re used to the former and not the latter:
Most significantly, the FROM clause in FQL can include only a single table, so there’s no official support for joins of any kind. You can sort of get around this by using subqueries, but note that the subqueries can’t reference variables from the outer query’s scope. An example of a very useful subquery: requesting all of the friends of the current user who have already installed your application so that you can exclude them from an fb:multi-friend-selector control:
SELECT uid FROM user WHERE has_added_app=1 and uid IN
(SELECT uid2 FROM friend WHERE uid1 = $user)
In order for Facebook to provide direct database access for apps, all queries have to be indexable so that they don’t impose huge performance hits on the server. Facebook maintains a relatively short list of 17 tables that you can do queries on; see Indexed Facebook Tables and Fields for details.
NOTE
Indexing is a technique in which a lookup index is created on a specific column in a table in order to speed up queries where that column is in the WHERE clause. (That makes it much clearer, no?) It might help to think of a giant library in which all the books are arranged in alphabetical order by title. If you wanted to find all of the books I’ve written, you’d have to go through the entire library and look for my name on the spines—slow and highly inefficient. However, if all the books were numbered and I handed you an index organized by author, you could look me up, note all the IDs of the books I’ve written, and then walk directly up to them on the shelf. Of course, this query would be very quick because this is my first book, so you wouldn’t have far to walk.
One critical note: the indexing limitation isn’t that your query can only use indexed columns in the WHERE clause, but rather that at least one of them has to be indexed. This is not explained very well in the Developers Wiki, which may lead you to conclude that you can query only on indexed columns. For example, if you’re looking for all of the users who share my birthday, you can’t do:
SELECT uid FROM user WHERE strpos(birthday, "September 27") = 0;
since birthday isn’t an indexed field. You can, however, do:
SELECT uid FROM user WHERE strpos(birthday, "September 27")
= 0 AND uid IN (SELECT uid2 FROM friend WHERE uid1 = 561415460)
which will find all of my friends with that birthday. Since the first query would have been automatically constrained to people whose birthdays I’m allowed to see anyway, they have almost the same result.
Given that one of the goals of FQL is to reduce the amount of data exchanged, there is no support for SELECT *. Down with lazy programmers! You’re just going to have to figure out the actual fields you want and list them in each query.
Finally, note that the ORDER BY capability in FQL is limited to a single field rather than the multiple comma-separated fields supported by SQL. You’ll get a 601 error (“Parser error: unexpected ',’”) if you try to sneak one in.
Functions and Operators
Problem
What functions and operators are supported in FQL?
Solution
FQL supports a number of functions that will be very familiar to PHP developers (see Table 8-1).
Table 8-1. FQL functions
Function
Description
now()
Returns the current time
rand()
Generates a random number
strlen(string)
Returns the length of the string
concat(string,