pagination - FQL : Limit and Offset variance return unexpected results

Question

FQL 很难。确保它返回所有可能的结果是我能想到的最神秘的练习之一。考虑这些仅限制和偏移量不同的查询及其返回结果:

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

返回 400 个结果。好的，很酷。

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

返回 0 个结果。嗯，也许我只有 400 张照片。让我们确认一下:

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

返回 357 个结果。扫管笏。其他 43 个结果去哪儿了？让我降低限制并翻页:

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

返回 0 个结果？？？哦来吧。

谁能解释一下？我正在拔头发。

Answer 1

进一步评论@user15 来源:https://developers.facebook.com/blog/post/478/

解释:

具体应用于你的例子:

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.

这意味着在某些不连贯的数据(图片中的#3)中，您总共可以获得 400 个结果。

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 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.

因此看起来使用 limit 你会在图中得到 #3，但是当使用 offset 时你可能会得到类似 #2 的结果；这意味着您的偏移量可能会将您置于 #2 的红色区域之一，这意味着该帖子对用户不可见，并且不会在您的数据集中(返回 0 个结果)。

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

来自他们的文字:

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

看我对你第二个问题的回答

解决方案:

根据我对您关于如何简化原始查询的问题的评论:

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

这个查询是说检查我所有相册中的所有照片，其中创建日期早于某个时间(时间戳)。这是正确的吗？

我可以看到您正在做的两种可能的解决方案:

1. 查看这些照片有哪些权限限制正在从结果集中删除，您可能需要修改您的查询以包括这些:

   来自: 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. 我认为这是最适合您的。你可能需要选择一个足够低的限制，例如 20 或 50，然后调整查询周围的时间戳，例如:

   上一个:

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

   下一步:

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

如果其中任何一个对您有用，请告诉我。请注意，我编造了第二个时间戳，您必须弄明白。