Home & blog  /  Tag: SQL  /

posted: 18 Aug '11 23:23 tags: Facebook, FQL, comments, SQL

There's no question is handy. The power and familiarity of the FB posts system, coupled with the fact that most internet users have Facebook logins these days, is hard to ignore when you need a comments system for a site.

Shame its API is horrible.

Oh sure, with a snippet of code you get comments on your site.

But when you actually want to start doing things from a developer point of view with those comments, it gets unfriendly.

Comments by URL

For one thing, who on earth at FB decided it the foreign key in the comments database table should be the URL of the page that was commented on, not the app ID? After all, on Facebook itself, comments are logged by object_id - aka the object on FB (wallpost, video etc) that the comment pertains to. So why go with URLs for external sites?

Hesitant as I am to question the development behind one of the internet's most visited site, every elementary developer knows database tables should trade on IDs, never strings.

There are two major problems with this.

First, what if the URL changes? And they often do - particularly if you use mod re-writes, as many sites (particularly blogs, including mine) do. There, the majority of the URL is unimportant; only the ID matters. The rest of the URL is cosmetic and, as you can see in the URL for this page, made up of the blog post title. If I tweaked that title, and thus the URL changed, any comments would become disassociated with it.

The workaround here is to log the comments under a permalink, i.e. a link that will not change. So for example for the page you're on right now, rather than mitya.co.uk/blog/month/post-title-here-999, I might log the comments under a duller URL like mitya.co.uk/blog999.

That way, even if I change tweak the title of the post, the comments won't be lost.

Comments for whole site/section? No...

I said there were two problems. The second is that comments can be retrieved only for a specific page - not, say, for an entire site. This is done with some simple FQL (Facebook Query Language - a sort of primitive SQL).

select text from comment where object_id in (select comments_fbid from link_stat where url = "{PAGE URL}")

FQL does not support the like operator, so you can't do this:

select text from comment where object_id in (select comments_fbid from link_stat where url like "{DOMAIN}%")

No - if you want to retrieve all comments for your site, you must implicitly stipulate each and every page on your site that you has comments on. As if that wasn't ugly enough, this raises the problem: how do I know which pages on my site have had comments posted? In short, you dont.

Workaround

But you can, though it's more work than you might imagine. Facebook fires an event, caught in the Javascript SDK, whenever a comment is posted. You can detect it like so:

1FB.Event.subscribe('comment.create', function(data) {

2     //do something here

3});

FB passes an object into data, containing two properties - href and commentID.

Armed with this you can pass this info out to a file or database, thus logging the URLs that are commented on. See where this is going? You can then retrieve this data when you wish to get all comments for the whole site, resulting in an FQL query like:

select text from comment where object_id in (select comments_fbid from link_stat where url = "{PAGE URL}" or url = "{PAGE URL 2}" or url = ""{PAGE URL 3}") //etc

FQL multiqueries - retrieving meaningful data

I said FQL was primitive, and one reason is it doesn't support table joins - except for using the in operator, which as any SQL developer will know is sort of a 'join lite'.

Yes, it joins tables, but the joined table is interrogated only - no data is retrieved from it. And only one column can be interrogated. All of which means that, if you want to retrieve meaningful data from FB, you need multiqueries.

1var fql = {

2     urls: "select url, comments_fbid from link_stat where url = '{URL 1}' or url = '{URL 2}'",

3     comments: "select post_fbid, fromid, object_id, text, time from comment where object_id in (select comments_fbid from #urls) order by time desc limit 10",

4     users: "select id, name from profile where id in (select fromid from #comments)"

5}

There, I create an object containing 3 separate queries which will be used to retrieve comments data - one to get the comment itself, another to get data on the user that left it, and another to get the URL the comment was left for.

As you can see, the queries, whilst separate, can reference each others' resultsets by referencing the object property name assigned to each query, prefixed with #.

We then run these through the FB Javascript SDK:

1FB.api({method: 'fql.multiquery', queries: fql}, function(data) {

2     //do something with returned

3});

The obvious drawback is this: 3 queries means 3 resultsets, compared to a real join which would of course merge the resultsets for us. As it is, we need to do that ourselves.

1FB.api({method: 'fql.multiquery', queries: fql}, function(data) {

2

3     //log returned data from queries

4     var urls = data[0].fql_result_set, comments = data[1].fql_result_set, users = data[2].fql_result_set;

5    

6     //re-jig useres & URLs data into {id: value} objects

7     var usersMap = {}, urlsMap = {};

8     for(var i in urls) urlsMap[urls[i].comments_fbid] = urls[i].url;

9     for(var i in users) usersMap[users[i].id] = users[i].name;

10    

11     //iterate over comments and output them, grabbing associated user & URL data as we od

12     for(var i in comments) {

13         $('#latestComments ul').append($('<li>', {text: usersMap[comments[i].fromid]+' - '+urlsMap[comments[i].object_id]+' - '+comments[i].text}));

14     }

15});

The 're-jig' bit is the most interesting bit there, and it's crucial to getting meaningful data from FQL queries.

The comments table relates to the other 2, even though we can't join them in the traditional sense. It conatins a foreign key to the profile table (fromid), and a foreign key to the link_stat table (object_id). This means we simply need to merge the data together, as I do above, so it lives in one, big, happy object.

So, all in all not super friendly. But after spending some hours reading the documentation (which is often VERY lacking) and playing with the , you can get results.

|