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.

因此，看起来在有限制的情况下，您将在图中得到#3，但是当使用偏移量时，您可能会得到类似#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;

请告诉我这些方法是否适合您。请注意，我编造了第二个时间戳，您必须弄清楚这一点。