Increasing the performance and usability of Facebook's FQL

Question

I try to get some insights from the pages I am administrator on Facebook.
What my code does, it gets the IDs of the pages I want to work with through mySQL. I did not include that part though.

After this, I get the page_id, name and fan_count of each of those facebook IDs and are saved in fancounts[].

I have two problems with it.

1. It has a very slow performance
2. I can't find a way to echo the data like this:

My questions are, how can the code be modified to increase performance and show the data as above? I read about fql.multiquery. Can it be used here?

Please provide me with code examples. Thank you

Answer 1

At the moment, you're making two separate calls to Facebook's database which is slowing everything down. Facebook offer their multiquery so that you can do everything in as few DB calls as possible. So the calls you should think about using are:

"query1":"SELECT page_id, name, fan_count FROM page WHERE page_id IN ($pagesIds)"

And because they allow you to reference a prior query, you can just include it after a #:

"query2":"SELECT message FROM stream WHERE source_id IN (SELECT page_id FROM #pages) LIMIT 2"

The PHP you need to use is something like this:

$query = array(
    "pages"=>"SELECT page_id, name, fan_count FROM page WHERE page_id IN ($pagesIds)",
    "messages"=>"SELECT message FROM stream WHERE source_id IN (SELECT page_id FROM #pages) LIMIT 2"
);

$fql_url = $facebook->api(array(
    'method' => 'fql.multiquery',
    'queries' => $query
));

print_r($fql_url);

If the second query isn't going through, try testing the FB DB with just this query and see if it works. If the query doesn't return anything by itself, the problem might be with permissions (i.e. accessing a sensitive table -- but I don't think this is the case). Another problem which I've frequently encountered is how FQL trips itself up with whitespace, so try omitting all possible whitespace from your array:

$query = array("pages"=>"SELECT page_id, name, fan_count FROM page WHERE page_id IN ($pagesIds)","messages"=>"SELECT message FROM stream WHERE source_id IN (SELECT page_id FROM #pages) LIMIT 2");

Wow, that's readable... This was taken from Facebook's documentation on FQL, though, so you might need to adapt it for your web application if using a third party library. All of your data is saved in $fql_url. All you need to do is loop through it and echo out the information you want. If you want to see a summary of everything it contains, think about using print_r() or var_dump() just to get your bearings.

EDIT

The reason that you're receiving an empty array for the second query is because you don't seem to have permissions for the stream table. If you check Facebook's documentation, they mention the criteria needed to access this table:

To read the stream table you need

• read_stream permissions for all posts that the current session user is able to view
• read_insights permissions to see post impressions for any posts made by a Page owned by the current session user

To check what permissions you have, you can run this query:

$check_query = $facebook->api(array(
    "method"    => "fql.query",
    "query"     => "SELECT * FROM permissions WHERE uid=me()"
));

foreach($check_query[0] as $k => $v) {
    if($v === "1") {
        echo "<strong>$k</strong> permission is granted.<br>";
    } else {
        echo "<strong>$k</strong> permission is not granted.<br>";
    }
}

Answer 2

If you have n pages, your script makes n+1 queries. This is the main drawback of your script. This is the reason for low performance.

You can use batch request to combine the queries. You can use the below script to achieve what you want. I have combined those n+1 queries to just one batch query. So it will be mush faster than your script.

I've also corrected the echo part. Now the script will display the output as you stated in your question.

// Get the IDs
$pages = array(); 
$pagesIds = implode(',', $pages);

// fancounts[] holds the page_id, name and fan_count of the Ids I work with
$fancounts = array();
$q = "SELECT page_id, name, fan_count FROM page WHERE page_id IN ({$pagesIds})";
$queries[] = array('method'=>'GET', 'relative_url' => 'method/fql.query?query=' . urlencode($q) );

$messages = array();
foreach( $pages as $id) 
{
   $q = "SELECT message FROM stream WHERE source_id = '$id' LIMIT 2";
   $queries[] = array('method'=>'GET', 'relative_url' => 'method/fql.query?query=' . urlencode($q) );
}

// The batch query
$batchResponse = $facebook->api('?batch='.json_encode($queries), 'POST');
$pagesFanCounts = json_decode($batchResponse[0]['body'], TRUE);

foreach ($pagesFanCounts as $page)
{       
   $fancounts[] = number_format($page['page_id'],0,'','')."-".$page['name']."-".$page['fan_count'];
}

for($i=0; $i < count($fancounts); $i++) 
{
   echo '</br>',$fancounts[$i],'<br>';
   $temp = json_decode($batchResponse[$i+1]['body'], TRUE);
   foreach ($temp as $msg)
   {
      echo ($msg['message']);
      echo "</br>";
   }
}