FQL: Limit and Offset variance return unexpected results

Question

FQL is hard. Making sure it returns all possible results is one of the most mystifying exercises I can think of. Consider these queries, that only differ by limit and offset, and their returned results:

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND 0 < created AND created < 1299952078 LIMIT 400 OFFSET 0

Returns 400 results. Okay, cool.

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND 0 < created AND created < 1299952078 LIMIT 400 OFFSET 400

Returns 0 results. Hm, maybe I only have exactly 400 photos. Let's just confirm that:

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND 0 < created AND created < 1299952078 LIMIT 500 OFFSET 0

Returns 357 results. Wat. Where's the other 43 results go? Let me lower the limit and page through:

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND 0 < created AND created < 1299952078 LIMIT 300 OFFSET 300

Returns 0 results??? Oh c'mon.

Can anyone explain that? I'm pulling my hair out.

Answer 1

Commenting further on @user15 source: https://developers.facebook.com/blog/post/478/

Explanation:

Specifically applied to your example:

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
AND 0 < created AND created < 1299952078 LIMIT 400 OFFSET 0
Returns 400 results. Okay, cool.

This means that at some disjointed data (#3 in the picture) you were able to get 400 total results.

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND 0 < created AND created < 1299952078 LIMIT 400 OFFSET 400
Returns 0 results

This comes right from their text:

This also means when you are manually constructing your own queries, you should be aware that with some tables and connections if you are specifying an “offset” parameter, the Nth result you are pointing to may not get returned in your results (like the 3rd result in step 2 of the image above).

One tricky issue is determining if you have reached the end of the result set. For example, if you specified a limit of “5” but the five posts returned are not visible to the viewer, you will get an empty result set.

Thus it looks like with limit you will get #3 in the graph, but when using offset its possible you will end up with something like #2; which means your offset may put you in one of the red areas of #2, meaning the post is invisible to the user and will not be in your data set (0 returned results).

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND 0 < created AND created < 1299952078 LIMIT 500 OFFSET 0
Returns 357 results

From their text:

Query parameters are applied on our end before checking to see if the results returned are visible to the viewer. Because of this, it is possible that you might get fewer results than expected.

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND 0 < created AND created < 1299952078 LIMIT 300 OFFSET 300
Returns 0 results

See my answer to your second query

Solution:

As per my comment on your question of how to simplify your original query:

SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me())
  AND created < 1299952078 LIMIT 400;

This query is saying check for all photos, in all of my albums, where the created date is before some time (timestamp). Is this correct?

Two possible solutions I could see you doing:

1. Check to see what permission restrictions there are on those photos that are getting removed from the result set, you might need to rework your query to include these:

   From: https://developers.facebook.com/docs/reference/fql/photo

   Permissions

   To read the photo table you need

   • any valid access_token if it is public and owned by the Page.
   • user_photos permissions to access photos and albums uploaded by the user, and photos in which the user has been tagged.
   • friends_photos permissions to access friends' photos and photos in which the user's friends have been tagged.

2. This is the one I think will work the best for you. You might need to pick a sufficiently low limit like 20 or 50, and adjust the timestamp around your query such as:

   Previous:

   SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me()) AND created < 1299952078 LIMIT 20;

   Next:

   SELECT caption FROM photo WHERE aid IN (SELECT aid FROM album WHERE owner = me()) AND created < 1298123456 LIMIT 20;

Let me know if either of these work for you. Please note I made up the second timestamp, you will have to figure that out.

Answer 2

FQL query will return only the results that are visible to you, with permissions you have asked. So it is possible to get less results than the LIMIT you specified.

If you use

... LIMIT 400 OFFSET 0

you get the first 400 results between index 0 and 400. But if you use

... LIMIT 400 OFFSET 400

you're asking for results between 0 and 400, with offset of 400, which will always return 0 results. If you want next 400 results, you should use

... LIMIT 400,800