Using the Facebook Query Language (FQL)

PIN

Lots of people often want to use the Facebook API for many reasons – get some user data, social plugin, insight access, etc. The most widely used method seems to be the FB graph API. Although the Graph API is quite simple to use, it gets a bit clumsy if one needs a complex request or even to filter out some parts of data. Let us say, for example, we need to gather the feeds from our news feed. We could simply use the graph API to make a call to https://graph.facebook.com/{used_id}/home and conveniently receive something like this:

{
  "data": [
    {
      "id": "22893372268_10151177531767269", 
      "from": {
        "name": "BMW", 
        "category": "Cars", 
        "id": "22893372268"
      }, 
      "message": "You're never too young to drive an oldtimer.\r\n\r\nhttp://bit.ly/MI8kAN", 
      "picture": "https://fbcdn-photos-a.akamaihd.net/hphotos-ak-snc6/224366_10151177531712269_1317578396_s.jpg", 
      "link": "https://www.facebook.com/photo.php?fbid=10151177531712269&set=a.352379437268.193008.22893372268&type=1&relevant_count=1", 
      "icon": "https://s-static.ak.facebook.com/rsrc.php/v2/yz/r/StEh3RhPvjk.gif", 
      "actions": [
        {
          "name": "Comment", 
          "link": "https://www.facebook.com/22893372268/posts/10151177531767269"
        }, 
        {
          "name": "Like", 
          "link": "https://www.facebook.com/22893372268/posts/10151177531767269"
        }
      ], 
      "type": "photo", 
      "object_id": "10151177531712269", 
      "created_time": "2012-08-15T16:44:01+0000", 
      "updated_time": "2012-08-15T16:44:01+0000", 
      "shares": {
        "count": 1501
      }, 
      "likes": {
        "data": [
          {
            "name": "Yamkung TheCeleb", 
            "id": "1477550941"
          }, 
          {
            "name": "蕭力豪", 
            "id": "100000176505274"
          }, 
          {
            "name": "高培偉", 
            "id": "100001131514078"
          }, 
          {
            "name": "Aleko Chaduneli", 
            "id": "100000974253764"
          }
        ], 
        "count": 15090
      }, 
      "comments": {
        "count": 301
      }
    }, 
...

There may be two things problematic with this response.

  1. This may be much more data than we actually need, in which case we are unnecessarily sending bigger requests to the server every time we need new information.
  2. Notice that the above response has a post_id and an actor_id for each feed. What happens if we need the name of the poster (actor_name)? We would have to make a request to the graph API for every single feed item. That’s a lot of requests!

A much better solution would be the Facebook Query Language (FQL), which is basically a subset of SQL. It comes with the following major advantages over the Graph API:

  1. It allows you to query against specific tables. For instance, the user table for users, the stream table for any sort of feeds, so on and so forth.
  2. It allows multi-query in a single request. Although it still does not give the power of JOIN like SQL does, it will let you write sub-queries and use almost all of the major SQL operations which can be extremely helpful when querying multiple tables. And all this, in a single request!

Lets get started then! We will build a simple ASP.net MVC3 application that will access the Facebook via the FQL using multiple queries, get the newsfeed of a certain user (in this case, mine), and then parse it out into a view. To keep things simple, I have not included anything regarding obtaining access_tokens via client-side or server-side. If you are interested in that, the FB documentation does a pretty good job here: https://developers.facebook.com/docs/authentication/

Before we begin, you will need:

  • Log in to http://developers.facebook.com
  • Create a test application – Just fill out the basic information for now and you should be good.
  • Go to the graph explorer: https://developers.facebook.com/tools/explorer
  • Click in Get Access Token to obtain an access token. Make sure you check all options and all three permission tabs in the dialog that pops up so that your app will have full access to your Facebook. For the news feed however, the minimum amount of access required is all user_{action} and read_stream. Once, done, copy the access code and save it for later use. Note: This access_token will expire within one or two hours, so if you get an OAuth Exception later, you will have to retrieve a new access token following these steps again. The permissions dialog looks like this:

So now that you have your access_token, we are ready to begin. Let us first start a new MVC3 project and then we will proceed to build out each of the three MVC parts.

MODEL

Since the news feed is comprised for the entire stream, which will consist of individual feeds which will further contain properties and perhaps media as well, the following model should form a pretty simple, but a representative model of our Facebook feed.

public class FeedsViewModel
{
    public List<Newsfeed> Newsfeed { get; set; }
    public List<Tweet> Tweets { get; set; }
    public bool Error { get; set; }
}

public class Newsfeed
{
    public string PostId { get; set; }
    public long ActorId { get; set; }
    public string ActorName { get; set; }
    public string Summary { get; set; }
    public string Type { get; set; }
    public DateTime Time { get; set; }
    public int UnixTime { get; set; }
    public string Link { get; set; }
    public List<FBMedia> Media { get; set; }
    public string Description { get; set; }
}

public class FBMedia
{
    public string Name { get; set; }
    public string Alt { get; set; }
    public string Src { get; set; }
    public string Caption { get; set; }
    public string Type { get; set; }
}

CONTROLLER

Our controller needs to do the following this:

  1. Connect to the Facebook API with our access_token
  2. Parse the response into an acceptable data structures and map them to our model
  3. Do any required conversions along the way (dates and times etc.)

To achieve that, we will need a few extra tools. You may choose the library of your choice, but I have chosen the Facebook C# SDK and NewtonSoft’s Json Parser to achieve our controller goals. To install this simply use the Package Manager Shell:

  1. PM> Install-Package Facebook
  2. PM> Install-Package Newtonsoft.Json

Once you have those references, the following code should be the basic boilerplate to get the news feed.

public class FeedController : Controller
{
    #region Post Types
    /// <summary>
    /// Post Types
    /// </summary>
    private enum PostType
    {
        GroupCreated = 11,
        EventCreated = 12,
        StatusUpdate = 46,
        PostWallAnotherUser = 56,
        NoteCreated = 66,
        LinkPosted = 80,
        VideoPosted = 128,
        PhotosPosted = 247,
        AppStory1 = 237,
        CommentCreated = 257,
        AppStory2 = 272,
        Checkin = 285,
        PostInGroup = 308,
        TaggedBySomeoneElse = 65,
        Other = 0
    };
    #endregion

    #region Properties

    private bool Error { get; set; }

    private string AppKey
    {
        get
        {
            return ConfigurationManager.AppSettings["app_key"];
        }
    }

    private string AppSecret
    {
        get
        {
            return ConfigurationManager.AppSettings["app_secret"];
        }
    }

    private string FBAccessToken
    {
        get
        {
            return ConfigurationManager.AppSettings["fb_access_token"];
        }
    }

    private string ConsumerKey
    {
        get
        {
            return ConfigurationManager.AppSettings["consumer_key"];
        }
    }

    private string ConsumerSecret
    {
        get
        {
            return ConfigurationManager.AppSettings["consumer_secret"];
        }
    }

    private string TWAccessToken
    {
        get
        {
            return ConfigurationManager.AppSettings["tw_access_token"];
        }
    }

    private string TWAccessSecret
    {
        get
        {
            return ConfigurationManager.AppSettings["tw_access_secret"];
        }
    }

    #endregion

    #region Actions

        #region Index

        public ActionResult Index()
        {
            FeedsViewModel feed = new FeedsViewModel();
            feed.Newsfeed = GetNewsfeed();
            feed.Tweets = GetTweets();
            feed.Error = Error;
            return View(feed);
        }

        #endregion

        #region Get Newsfeed
        public List<Newsfeed> GetNewsfeed()
        {
            //Get access_token   
            string access_token = FBAccessToken;
            //New list of feeds
            List<Newsfeed> newsfeed = new List<Newsfeed>();
            //Make connection to FB
            var client = new FacebookClient(access_token);
            //Create new Json Object
            JObject o = new JObject();
            //Get News Feed
            try
            {
                dynamic request = client.Get("fql",
                    new
                    {
                        q = new
                        {
                            query1 = "SELECT post_id, actor_id, message, attachment, type, created_time FROM stream WHERE filter_key in (SELECT filter_key FROM stream_filter WHERE uid=me() AND type='newsfeed') AND is_hidden = 0 AND created_time > 1344959743 LIMIT 200",
                            //query2 = "SELECT uid, name FROM user WHERE uid IN (SELECT actor_id FROM #query1)",
                            query2 = "SELECT page_id, name FROM page WHERE page_id IN (SELECT actor_id FROM #query1)"
                        }
                    });
                o = JObject.Parse(Convert.ToString(request));
            }
            catch (Exception e)
            {
                ModelState.AddModelError("Exception", e.Message);
                Error = true;

            }

            //Get feed count
            Int32 Count = 0;
            if (o.SelectToken("data[0].fql_result_set") != null)
            {
                String CountValue = o.SelectToken("data[0].fql_result_set").Count().ToString();
                Count = Int32.Parse(CountValue);

                //Parse each feed item and add to list
                for (int i = 0; i < Count; i++)
                {
                    try
                    {
                        string _postId = (o.SelectToken("data[0].fql_result_set[" + i + "].post_id") == null) ? String.Empty :
                            (string)o.SelectToken("data[0].fql_result_set[" + i + "].post_id");
                        long _actorId = (long?)o.SelectToken("data[0].fql_result_set[" + i + "].actor_id") ?? 0;
                        string _actorName = FindName(o, (long)o.SelectToken("data[0].fql_result_set[" + i + "].actor_id"));
                        string _summary = (o.SelectToken("data[0].fql_result_set[" + i + "].message") == null) ? String.Empty :
                            (string)o.SelectToken("data[0].fql_result_set[" + i + "].message");
                        string _type = GetPostType((int)o.SelectToken("data[0].fql_result_set[" + i + "].type"));
                        DateTime _time = UnixTimeStampToDateTime((double)o.SelectToken("data[0].fql_result_set[" + i + "].created_time"));
                        int _unixTime = (int) o.SelectToken("data[0].fql_result_set[" + i + "].created_time"); 
                        string _link = (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.href") == null) ? String.Empty :
                            (string)o.SelectToken("data[0].fql_result_set[" + i + "].attachment.href");
                        string _description = (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.description") == null) ? String.Empty :
                            (string)o.SelectToken("data[0].fql_result_set[" + i + "].attachment.description");

                        //New list of feed media
                        List<FBMedia> fbmedia = new List<FBMedia>();
                        //Get Media count
                        Int32 MediaCount = 0;
                        if (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.media") != null)
                        {
                            String CountValueMedia = o.SelectToken("data[0].fql_result_set[" + i + "].attachment.media").Count().ToString();
                            MediaCount = Int32.Parse(CountValueMedia);

                            if (MediaCount > 0)
                            {
                                for (int j = 0; j < MediaCount; j++)
                                {
                                    string mediaName = (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.name") == null) ? String.Empty :
                                        (string)o.SelectToken("data[0].fql_result_set[" + i + "].attachment.name");
                                    string mediaSrc = (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.media[" + j + "].src") == null) ? String.Empty :
                                        (string)o.SelectToken("data[0].fql_result_set[" + i + "].attachment.media[" + j + "].src");
                                    string mediaAlt = (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.media[" + j + "].alt") == null) ? String.Empty :
                                        (string)o.SelectToken("data[0].fql_result_set[" + i + "].attachment.media[" + j + "].alt");
                                    string mediaCaption = (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.caption") == null) ? String.Empty :
                                        (string)o.SelectToken("data[0].fql_result_set[" + i + "].attachment.caption");
                                    string mediaType = (o.SelectToken("data[0].fql_result_set[" + i + "].attachment.type") == null) ? "Other" :
                                        (string)o.SelectToken("data[0].fql_result_set[" + i + "].attachment.type");

                                    fbmedia.Add(new FBMedia
                                    {
                                        Name = mediaName,
                                        Src = mediaSrc,
                                        Alt = mediaAlt,
                                        Caption = mediaCaption
                                    });
                                }
                            }
                            else
                                fbmedia = Enumerable.Empty<FBMedia>().ToList();
                        }

                        newsfeed.Add(new Newsfeed
                        {
                            PostId = _postId,
                            ActorId = _actorId,
                            ActorName = _actorName,
                            Summary = _summary,
                            Type = _type,
                            Time = _time,
                            UnixTime = _unixTime,
                            Link = _link,
                            Media = fbmedia,
                            Description = _description
                        });
                    }
                    catch (Exception e)
                    {
                        ModelState.AddModelError("Exception", e.Message);
                        Error = true;
                    }

                }
                return newsfeed;
            }
            else
                return Enumerable.Empty<Newsfeed>().ToList();
        }
        #endregion

#region Feeds Helper Methods

        /// <summary>
        /// Gets the string value of a post type
        /// </summary>
        /// <param name="value">int value of post</param>
        /// <returns>string value</returns>
        public static string GetPostType(int? value)
        {
            int val = value ?? 0;
            //bool convert = Int16.TryParse(value, out val);
            PostType _postType = ((PostType)val);
            string _postTypeString = _postType.ToString();
            if (_postTypeString.Equals(String.Empty))
                return "Other";
            else
                return _postTypeString;
        }

        /// <summary>
        /// Find a matching name for all feed poster ids
        /// </summary>
        /// <param name="query">FB Request Json Object</param>
        /// <param name="val">Poster Id</param>
        /// <returns>Poster Name</returns>
        public static string FindName(JObject query, long? val)
        {
            string actorName = "";
            long id = val ?? 0;
            if (id == 0)
                return "";
            else
            {

                Int32 InnerCount = 0;
                if (query.SelectToken("data[1].fql_result_set") != null)
                {
                    String CountValue = query.SelectToken("data[1].fql_result_set").Count().ToString();
                    InnerCount = Int32.Parse(CountValue);
                }
                for (int i = 0; i < InnerCount; i++)
                {
                    try
                    {
                        if ((long)query.SelectToken("data[1].fql_result_set[" + i + "].page_id") == val)
                        {
                            actorName = (string)query.SelectToken("data[1].fql_result_set[" + i + "].name");
                            break;
                        }
                    }
                    catch (Exception e) 
                    {
                        throw e;
                    }
                }
                return actorName;
            }
        }

        /// <summary>
        /// Converts Unix Time into DateTime
        /// </summary>
        /// <param name="unixTimeStamp">Unix Time Stamp</param>
        /// <returns>DateTime</returns>
        public static DateTime UnixTimeStampToDateTime(double? unixTimeStamp)
        {
            double time = unixTimeStamp ?? 0; // Unix timestamp is seconds past epoch
            System.DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0);
            dtDateTime = dtDateTime.AddSeconds(time).ToLocalTime();
            return dtDateTime;
        }

    #endregion

 

VIEW

I have added a tiny bit of exception handling into the view, so that it was easier to track what was going. If you are planning on publishing, perhaps using a custom error page is better than showing the users the actual stack trace 🙂 Well, here is the view.

@model com.utsavized.Feeds.Models.FeedsViewModel

@{
    ViewBag.Title = "Index";
}

<h2>Feeds</h2>
@if (Model.Error)
{ 
    <div>
        <ul>
            @foreach (ModelState modelState in ViewData.ModelState.Values)
            {
                foreach (ModelError error in modelState.Errors)
                {
                    <li>@error.ErrorMessage</li>
                }
            }
        </ul>
    </div>
}
else
{
    <div>
        @foreach (var f in Model.Newsfeed)
        {
            <div>
                <br /> Name: @f.ActorName
                <br /> Summary: @f.Summary
                <br /> Link: <a href="@f.Link">@f.Link</a>
                @if (f.Media.Count > 0)
                {
                    <br /><span>Attachment: <br /></span>
                    foreach (var m in f.Media) 
                    {
                        <span>
                            <img src="@m.Src" alt="@m.Alt" />
                        </span>
                    }
                }
                <br /> Description: @f.Description
                <br /> Source: Facebook
                <br /> <hr />
            </div>
        }
    </div>
}

And that’s that! This should give give you a good headstart to the Facebook API with FQL! Good luck! Here is what the output looks like.