PostgreSQL的，奇OFFSET /限制行为（记录顺序） [英] postgresql, odd OFFSET/LIMIT behavior ( records order )

问题描述

所以基本上我有这个范围（SQL）：

so basically I have this scope(sql):

scope.to_sql
=> "SELECT  \"offers\".* FROM \"offers\" INNER JOIN \"cities_offers\" ON \"offers\".\"id\" = \"cities_offers\".\"offer_id\" WHERE \"cities_offers\".\"city_id\" = 2 AND \"offers\".\"category_id\" IN (2) AND (offers.category_id is NOT NULL) ORDER BY offers.discount desc LIMIT 25 OFFSET 0"

不知何故记录顺序对于上面的查询与同一台无极限不同，OFFSET：

Somehow the records order is different for the above query and the same one without LIMIT and OFFSET:

   scope[6]
=> #<Offer id: 8629 ...

scope.except(:offset, :limit)[6]
=> #<Offer id: 8729 ...

这两个8629和8729的记录有相同的折让值（我命令由属性）。

both 8629 and 8729 records have the same discount value ( the attribute I order by ).

请您指教，如果它能够保持相同的记录在这种情况下订购？

Could you please advice if it's possible to keep the same records ordering under these circumstances?

推荐答案

关系数据库设置为基础的，因此本质上无序的;的记录结果集中的顺序指定的仅按ORDER BY子句。如果两行对前pression在ORDER BY子句相同的值，那么在运行相同的查询两次可能会返回在不同的位置的行;改变查询加入LIMIT和OFFSET只是使事情变得更糟通过不同的顺序的可能性较大。

Relational databases are set-based and hence inherently unordered; the order of the records in a result set is specified only by the ORDER BY clause. If two rows have the same values for the expression in the ORDER BY clause, then running the same query twice may return those rows in different positions; altering the query by adding LIMIT and OFFSET just makes things worse by making a different order more likely.

如果您希望数据库给你行按照一定的顺序，您必须完全指定的ORDER BY子句中的顺序。你要添加更多的序呼叫你的范围：

If you want the database to give you rows in a certain order, you must fully specify the order in your ORDER BY clause. You have to add more to the order call in your scope:

...order('offers.discount desc, offers.created_at asc')

或类似的东西，这取决于特定的顺序，你需要的。

or something like that depending on the specific order that you need.

这篇关于PostgreSQL的，奇OFFSET /限制行为（记录顺序）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！