Birthday Friends
Problem
I need to find all of a specific user’s friends whose birthdays are on a given day using FQL.
Solution
SELECT uid FROM user WHERE strpos(birthday, "September 27") = 0 AND uid IN (SELECT uid2 FROM friend WHERE uid1 = $uid)
Discussion
Rather than supporting the LIKE
comparator from SQL, Facebook has
included the ability to run a small set of PHP-like functions in your
queries (see Functions and Operators). We’re using
the strpos()
function here, which returns the position of the needle
in the haystack (the first parameter—birthday—is known as the
haystack, and the second parameter—“September 27”—is known as the
needle). Since we’re looking for people who share the same birthday
and we don’t care about the year, we want to return results where the
string starts with “September 27”, which would mean it’s at position
0. In more traditional SQL, this query would have:
SELECT uid FROM user WHERE birthday LIKE "September 27%" AND uid IN (SELECT uid2 FROM friend WHERE uid1 = $uid)
You can, of course, substitute any day you’d like for “September 27”, which just happens to be my birthday (please email cakes and other goodies). This query comes from the excellent Facebook Developers Wiki Sample FQL Queries page, which you’ll find at http://wiki.developers.facebook.com/index.php/Sample_FQL_Queries.
Get Facebook Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.