I have a table in MySQL that I'm accessing from PHP. For example, let's have a table named THINGS:
我在MySQL中有一个表,我正在从PHP访问。例如,让我们有一个名为THINGS的表:
things.ID - int primary key
things.ID - int主键
things.name - varchar
things.name - varchar
things.owner_ID - int for joining with another table
things.owner_ID - 用于与另一个表连接的int
My select statement to get what I need might look like:
我的select语句可以得到我需要的东西:
SELECT * FROM things WHERE owner_ID = 99;
SELECT * FROM things WHERE owner_ID = 99;
Pretty straightforward. Now, I'd like users to be able to specify a completely arbitrary order for the items returned from this query. The list will be displayed, they can then click an "up" or "down" button next to a row and have it moved up or down the list, or possibly a drag-and-drop operation to move it to anywhere else. I'd like this order to be saved in the database (same or other table). The custom order would be unique for the set of rows for each owner_ID.
很简单。现在,我希望用户能够为从此查询返回的项目指定完全任意的顺序。将显示该列表,然后他们可以单击行旁边的“向上”或“向下”按钮,让它在列表中向上或向下移动,或者可以通过拖放操作将其移动到其他任何位置。我想将此订单保存在数据库(相同或其他表)中。对于每个owner_ID,自定义顺序对于行集是唯一的。
I've searched for ways to provide this ordering without luck. I've thought of a few ways to implement this, but help me fill in the final option:
我已经找到了提供这种订购的方法而没有运气。我已经想到了实现这个的几种方法,但是帮我填写最终选项:
- Add an INT column and set it's value to whatever I need to get rows returned in my order. This presents the problem of scanning row-by-row to find the insertion point, and possibly needing to update the preceding/following rows sort column.
- Having a "next" and "previous" column, implementing a linked list. Once I find my place, I'll just have to update max 2 rows to insert the row. But this requires scanning for the location from row #1.
- Some SQL/relational DB trick I'm unaware of...
添加一个INT列并将其值设置为我需要获取的顺序返回的行。这提出了逐行扫描以查找插入点以及可能需要更新前/后行排序列的问题。
有一个“下一个”和“上一个”列,实现一个链表。找到我的位置后,我只需更新最多2行即可插入行。但这需要扫描第1行的位置。
一些SQL /关系数据库技巧我不知道......
I'm looking for an answer to #3 because it may be out there, who knows. Plus, I'd like to offload as much as I can on the database.
我正在寻找#3的答案,因为它可能在那里,谁知道。另外,我想尽可能多地卸载数据库。
2 个解决方案
#1
2
From what I've read you need a new table containing the ordering of each user, say it's called *user_orderings*.
根据我的阅读,你需要一个包含每个用户排序的新表,比如它叫做* user_orderings *。
This table should contain the user ID, the position of the thing and the ID of the thing. The (user_id, thing_id) should be the PK. This way you need to update this table every time but you can get the things for a user in the order he/she wants using ORDER BY on the user_orderings table and joining it with the things table. It should work.
该表应包含用户ID,事物的位置和事物的ID。 (user_id,thing_id)应该是PK。这样你每次都需要更新这个表,但你可以按照他/她想要的顺序在user_orderings表上使用ORDER BY并将其与things表连接起来。它应该工作。
#2
0
The simplest expression of an ordered list is: 3,1,2,4. We can store this as a string in the parent table; so if our table is photos with the foreign key profile_id, we'd place our photo order in profiles.photo_order. We can then consider this field in our order by clause by utilizing the find_in_set() function. This requires either two queries or a join. I use two queries but the join is more interesting, so here it is:
有序列表的最简单表达式是:3,1,2,4。我们可以将它作为字符串存储在父表中;因此,如果我们的表是带有外键profile_id的照片,我们会将照片订单放在profiles.photo_order中。然后我们可以通过使用find_in_set()函数在order by子句中考虑这个字段。这需要两个查询或连接。我使用两个查询但是连接更有趣,所以这里是:
select photos.photo_id, photos.caption
from photos
join profiles on profiles.profile_id = photos.profile_id
where photos.profile_id = 1
order by find_in_set(photos.photo_id, profiles.photo_order);
Note that you would probably not want to use find_in_set() in a where clause due to performance implications, but in an order by clause, there are few enough results to make this fast.
请注意,由于性能影响,您可能不希望在where子句中使用find_in_set(),但在order by子句中,几乎没有足够的结果可以使其快速。
This information is trivially easy to update in your database, because somewhere in your system your user interface has to know the photo order, and will fall back to some default if no order exists in the database. So whatever the interface decides on, you just need to have it send a request to server with that order, whenever the user changes it.
这些信息在您的数据库中很容易更新,因为系统中的某个位置您的用户界面必须知道照片顺序,如果数据库中不存在任何订单,则会回退到某些默认值。因此无论接口决定什么,只要用户更改它,您就需要让它向该服务器发送一个请求。
#1
2
From what I've read you need a new table containing the ordering of each user, say it's called *user_orderings*.
根据我的阅读,你需要一个包含每个用户排序的新表,比如它叫做* user_orderings *。
This table should contain the user ID, the position of the thing and the ID of the thing. The (user_id, thing_id) should be the PK. This way you need to update this table every time but you can get the things for a user in the order he/she wants using ORDER BY on the user_orderings table and joining it with the things table. It should work.
该表应包含用户ID,事物的位置和事物的ID。 (user_id,thing_id)应该是PK。这样你每次都需要更新这个表,但你可以按照他/她想要的顺序在user_orderings表上使用ORDER BY并将其与things表连接起来。它应该工作。
#2
0
The simplest expression of an ordered list is: 3,1,2,4. We can store this as a string in the parent table; so if our table is photos with the foreign key profile_id, we'd place our photo order in profiles.photo_order. We can then consider this field in our order by clause by utilizing the find_in_set() function. This requires either two queries or a join. I use two queries but the join is more interesting, so here it is:
有序列表的最简单表达式是:3,1,2,4。我们可以将它作为字符串存储在父表中;因此,如果我们的表是带有外键profile_id的照片,我们会将照片订单放在profiles.photo_order中。然后我们可以通过使用find_in_set()函数在order by子句中考虑这个字段。这需要两个查询或连接。我使用两个查询但是连接更有趣,所以这里是:
select photos.photo_id, photos.caption
from photos
join profiles on profiles.profile_id = photos.profile_id
where photos.profile_id = 1
order by find_in_set(photos.photo_id, profiles.photo_order);
Note that you would probably not want to use find_in_set() in a where clause due to performance implications, but in an order by clause, there are few enough results to make this fast.
请注意,由于性能影响,您可能不希望在where子句中使用find_in_set(),但在order by子句中,几乎没有足够的结果可以使其快速。
This information is trivially easy to update in your database, because somewhere in your system your user interface has to know the photo order, and will fall back to some default if no order exists in the database. So whatever the interface decides on, you just need to have it send a request to server with that order, whenever the user changes it.
这些信息在您的数据库中很容易更新,因为系统中的某个位置您的用户界面必须知道照片顺序,如果数据库中不存在任何订单,则会回退到某些默认值。因此无论接口决定什么,只要用户更改它,您就需要让它向该服务器发送一个请求。