FQL:限制和偏移方差返回意外结果 [英] FQL: Limit and Offset variance return unexpected results

问题描述

FQL很难.确保返回所有可能的结果是我能想到的最神秘的练习之一.考虑以下查询，它们仅在限制和偏移量以及它们返回的结果上有所不同:

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

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

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

返回0个结果.嗯，也许我只有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

返回357个结果.笏.其他43个结果在哪里?让我降低限制并翻页:

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

返回0条结果???哦，来吧.

Returns 0 results??? Oh c'mon.

有人可以解释吗?我正在拔头发.

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

推荐答案

在@ user15来源上进一步评论:

Commenting further on @user15 source: 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个结果.

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:

这也意味着当您手动构建自己的查询时， 您应该知道，如果您有一些表和连接， 指定偏移"参数，即您指向的第N个结果 可能不会返回您的结果(例如，第2步的第3个结果 上图).

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

一个棘手的问题是确定您是否已到达末尾 结果集.例如，如果您指定的限制为"5"，但五个 查看者看不到返回的帖子，您将得到一个空白 结果集.

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，但是当使用offset时，您最终将得到#2；这意味着偏移量可能会使您进入#2的红色区域之一，这意味着该帖子对用户不可见，并且不会出现在您的数据集中(返回的结果为0).

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

从他们的文字中:

查询参数已应用于我们的之前，以检查是否 返回的结果对查看者可见.因此，它是 可能会得到比预期更少的结果.

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

解决方案:

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

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. 检查以查看这些照片有哪些权限限制 从结果集中删除的结果，您可能需要 重做您的查询以包括以下内容:

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:

来自: https://developers.facebook.com/docs/reference/fql /photo

权限

要阅读照片表

• 任何有效的access_token(如果它是公共的且由Page拥有).
• user_photos权限，用于访问用户上传的照片和相册以及标记了用户的照片.
• friends_photos权限，用于访问朋友的照片以及在其中标记了用户朋友的照片.

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

这是我认为最适合您的一种.您可能需要 选择一个足够低的限制(例如20或50)，并调整 查询周围的时间戳记，例如:

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:

上一个:

从照片辅助输入中选择标题(从相册辅助输入选择中 owner = me())并且创建< 1299952078 LIMIT 20;

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

下一步:

从照片辅助输入中选择标题(从相册辅助输入选择中 owner = me())并且创建< 1298123456 LIMIT 20;

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.

这篇关于FQL:限制和偏移方差返回意外结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！