如何让用户插入列表中的任何地方？ [英] How to let a user insert anywhere in a list?

问题描述

予有存储在数据库中的项目的列表。每个项目都有一个ID列，标题和位置列（INT）。

I have a list of items stored in the database. Each item has an id column, a title, and a position column (int).

在默认情况下，当用户添加了一个新的项目列表中，其ID被放入位置。所以，如果你有3项，在IDS 1，2，3，他们的职位将是1，2，3，也是如此。

By default, whenever the user adds a new item to the list, its id is put in the position. So if you have 3 items, with ids 1, 2, 3, their positions will be 1, 2, 3, as well.

然后获取的位置的时候，我会做 ORDER BY位置ASC 在我的SQL查询。

Then when fetching the positions, I will do ORDER BY position ASC within my SQL query.

的问题是，用户想要的特征，他可以在现有的物品内的任何位置添加新的项目。

The problem is, the user wants a feature where he can add a new item anywhere within the existing items.

因此​​，如果你有物品1，2，3与位置1，2，3，他可以选择来添加新的项目在位置2，这将随后导致物品1，2，3，4，具有位置：1 3 4 2

So if you have items 1, 2, 3 with positions 1, 2, 3, he could choose to add a new item at position 2, which will then result in items 1, 2, 3, 4, having the positions: 1 3 4 2

所以项目＃4将在2位，项＃2和3将被下推到位置3和4等被放

So item # 4 will be put at position 2, item # 2 and 3 will be pushed down to positions 3 and 4, etc.

什么是最简单/最有效的算法研究，以做到这一点插入？

What's the simplest / most efficient algorith to accomplish this inserting?

推荐答案

从code的角度来看，最简单的方法是使用一个链接列表样式，在这里你有下一个元素的ID，而非订单价值。这是在小名单用处不大，但是当你开始成长列表的大小，它使得从可能的更新数千元组的更新/移动/清除更清洁，prevents，使1级的变化。

The simplest method from a code standpoint is to use a linked-list style, where you have a next element id, rather than an order value. This is less useful in small lists, but when you begin to grow the list size, it makes updates/moves/removals much cleaner and prevents from possibly updating thousands of tuples to make 1 order change.

++我也没有这方面确切的code的例子，但你会作出一个呼吁所有列表中的用户的

++ I don't have an exact code example on hand, but you would make a call for all the list's of a user

SELECT Posts.id, Posts.next, Posts.content, User.firstPost FROM db.posts AS Posts 
JOIN db.user as User ON Posts.ownerid = User.id 
WHERE User.id='123' AND Posts.active = 1;

这会得到一个用户的所有岗位，那么你将有一个包含的下一个帖子的ID字段。要开始，你需要一个键来标识第一篇文章（存储在用户的信息的第一个帖子的ID）。当你拉的第一篇文章，你抓它的'下一个'值，并用它来确定下一个职位。

This would get all the posts of a single user, and then you would have a field that contains the id of the next post. To start you would need a key to identify the first post (store the id of the first post in the user's info). After you pull the first post, you grab it's 'next' value, and use that to identify the next post.

这似乎有点疯狂了短名单，但想想有1000个职位的用户。如果他们需要增加1后，在第2个位置。在传统的数字排序系统，你现在就需要更新999其他行有+1更新。有了这个解决方案，如果你需要插入第二位置后，只需查询后的第一个拿到它的下一个值。一旦你拥有了它，你改变它成为新的第二个职位的价值，并设置第二个职位的下一个值是一个最初由首次举办。

This seems a little crazy for a short list, but think about a user with 1000 posts. If they need to add 1 post in the 2nd position. In a traditional numeric ordering system, you would now need to update 999 other rows with a +1 update. With this solution, if you need to insert a post in the second position, you simply query the first post to get it's next value. Once you have it, you change it to be the value of the new second post, and set the second post's next value to be the one originally held by the first.

[1st Post] --Next---> [2nd Post] --Next---> [3rd Post]

插入后：

[1st Post] -.                          .--> [2nd Post] --Next---> [3rd Post]
            `--Next--> [New 2nd Post]--`

这篇关于如何让用户插入列表中的任何地方？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！