preface
In many user interaction scenarios, there are often requirements for users to provide data sorting, sorting interaction, front-end plug-ins and solutions can be realized, the problem is how to save the user’s sorting results.
Conventional scheme
The most common solution is to save the order of all sorted data or reorder assignments, which is acceptable for small amounts of data. Once the data is in the tens of thousands, the scheme starts to get a little cheesy.
So the author tried to design a general sorting scheme.
General plan
The first is to add a column of type double to the table for sorting. For example, this column is rankValue and the table name is tableItem.
So, we just need to design a sort interface to the front end, the front end drags the sort, passes the three parameters item_id, prev_ITEM_id, next_ITEM_id respectively, can be used to resort.
When you drag an item_id between prev_ITEM_id and next_item_id, recalculate the rankValue of the item_id so that it matches the upper and lower order values.
So, here’s the core question: how do you calculate the middle value between two rank values?
Fuzzy median
To calculate the middle value of two numbers, the first thought would be to do it directly (prevRankValue + nextRankValue) / 2.
Sure, (10+21)/2=15.5 looks like it, but with a few more drags:
(10+15.5)/2 = 12.75
(10+12.75)/2 = 11.375
Copy the code. This is going to be a weird situation that people with OCD can’t tolerate, so we need to create an algorithm that says:
(10+15.5)/2 ≈ 13
(10+12.75)/2 ≈ 11
Copy the codeI call this the fuzzy middle number, which is to take the fuzzy middle number of two numbers and ignore the exact value as much as possible. For example, the middle number between 0.99 and 1.2 is 1. The middle number between 9000 and 1002 is 950, not 951.
The reason for doing this is to make sure that our data does not get too ugly after a lot of dragging and reordering. Of course, if you want to insert 100 data between 1 and 2, that’s another story.
The core algorithm
Provide a PHP version of the code for your reference:
/** * math related processing function library file *@package W2
* @author wanyaxing
* @since 1.0
* @version1.0 * /
class W2Math {
/** Returns the exact number of digits, with positive numbers representing n decimal places and negative numbers representing ten million (10 to the (n-1) power) */
public static function getPrecisionOfNumber($number)
{
$number = abs($number);
$len = strlen($number);
if (strpos($number,'. ') > =0)
{
return $len - strpos($number,'. ') + 1;
}
else
{
for ($i=1; $i < $len; $i++)
{
if (substr($number,$len-$i,1) > 0)
{
return 0 - ($i - 1); }}}}/** * get a fuzzy middle number between two numbers, ignoring exact values as much as possible. For example, the middle number between 0.99 and 1.2 is 1 *@param [type] $bigNumber [description]
* @param [type] $smallNumber [description]
* @param boolean $isShortIfShortAble [description]
* @return [type] [description]
*/
public static function getMiddleBetweenNumbers($bigNumber=null,$smallNumber=null)
{
if(! is_null($bigNumber) || ! is_null($smallNumber)) {if (is_null($bigNumber))
{
$precision = min(W2Math::getPrecisionOfNumber($smallNumber),- 1);
return $smallNumber + pow(10,abs($precision));
}
else if (is_null($smallNumber))
{
$precision = min(W2Math::getPrecisionOfNumber($bigNumber),- 1);
return $bigNumber - pow(10,abs($precision));
}
else if ($bigNumber==$smallNumber)
{
return $bigNumber;
}
else if ($bigNumber<$smallNumber)
{
return null;
}
else
{
$middle = $smallNumber + (($bigNumber - $smallNumber)/2);
$precisionMin = min(W2Math::getPrecisionOfNumber($bigNumber),W2Math::getPrecisionOfNumber($smallNumber),- 1);
$precisionMax = max(W2Math::getPrecisionOfNumber($bigNumber),W2Math::getPrecisionOfNumber($smallNumber),$precisionMin);
for ($i=$precisionMin; $i <=$precisionMax ; $i++) {
$tmp = round($middle,$i);
if ($tmp>$smallNumber && $tmp<$bigNumber)
{
return$tmp; }}}}return null; }}Copy the codeNote that the getMiddleBetweenNumbers method takes strict rules that require large numbers to be first and small numbers to be second. You should ensure the order of the numbers in your business logic before passing them.
Interface implementation
Continue to provide a core code of the interface scheme for your reference:
// ItemHandler.php
class ItemHandler extends AbstractHandler {
/** get the intermediate sort value */ between the two commodity sorts
public static function getRankValueBetweenItems($prevItemID=null,$nextItemID=null)
{
$prevItemModel = ItemHandler::loadModelById($prevItemID);
$nextItemModel = ItemHandler::loadModelById($nextItemID);
if (is_object($prevItemModel) || is_object($nextItemModel) )
{
if(! is_object($prevItemModel)) { $prevItemModel = ItemHandler::loadModelFirstInList(array('rankValue > ' . $nextItemModel->getRankValue(),'status'=>$nextItemModel->getStatus()),'rankValue asc'.1.1);
}
if(! is_object($nextItemModel)) { $nextItemModel = ItemHandler::loadModelFirstInList(array('rankValue < ' . $prevItemModel->getRankValue(),'status'=>$prevItemModel->getStatus()),'rankValue desc'.1.1); } $bigNumber = is_object($prevItemModel)? $prevItemModel->getRankValue():null; $smallNumber = is_object($nextItemModel)? $nextItemModel->getRankValue():null;
return W2Math::getMiddleBetweenNumbers($bigNumber,$smallNumber);
}
return null; }}Copy the codeAs you can see here, both prev_ITEM_id and next_item_id are not mandatory. You can also pass only one parameter. In the interface, you can try to retrieve another data from the database, or if there is no number available, that is, when you want to drag the data to the first or last row of the entire mask.
// ItemController.php
class ItemController extends AbstractController{
public static function save($tmpModel,$isAdd=false)
{
if ($tmpModel->isProperyModified('status') && $tmpModel->properyValue('status')==STATUS_NORMAL)
{
$tmpModel->setRankValue(time());
}
return parent::save($tmpModel,$isAdd);
}
public static function actionResetRankValueOfItem(a)
{
if (static::getAuthIfUserCanDoIt(Utility::getCurrentUserID(),'axapi'.null) != 'admin')
{
return HaoResult::init(ERROR_CODE::$NO_AUTH);
}
$itemID = W2HttpRequest::getRequestInt('item_id');
$itemModel = ItemHandler::loadModelById($itemID);
if(! is_object($itemModel)) {return HaoResult::init(ERROR_CODE::$DATA_EMPTY);
}
$prevItemID = W2HttpRequest::getRequestInt('prev_item_id');// The previous one (its rankValue should be larger)
$nextItemID = W2HttpRequest::getRequestInt('next_item_id');// Next (its rankValue should be smaller)
$newRankValue = ItemHandler::getRankValueBetweenItems($prevItemID,$nextItemID);
$itemModel->setRankValue($newRankValue);
return static::save($itemModel); }}Copy the code$tmpModel->setRankValue(time()); $tmpModel->setRankValue(time()); $tmpModel->setRankValue();
The front-end implementation
Provide a front-end implementation of the core code, for your reference
- In the output table, bind each row to item_ID
<tbody>
foreach ($requestResult->results() as $detailResult) : ? >
<tr item_id="
find('id') ? >" >
<td><?= $detailResult->find('itemName') ? ></td>
</tr>
endforeach ? >
</tbody>
Copy the code- Use the sortable. js plugin to provide drag-and-drop function for table rows. After the drag-and-drop behavior is complete, call the interface with the item_id to save the sorting result.
<script type="text/javascript">
$(function(){
$LAB
.script('/third/haouploader/js/sortable/Sortable.js')
.wait(function(){
Sortable.create($('#item_list_bg tbody') [0] and {draggable:'tr'.animation: 150.// Changed sorting within list
onEnd: function (/**Event*/evt) {
if(evt.oldIndex ! = evt.newIndex) {var $this = $(evt.item);
var params = {};
params['item_id'] = $this.attr('item_id');
params['prev_item_id'] = $this.prev().attr('item_id');
params['next_item_id'] = $this.next().attr('item_id');
HaoConnect.post('item/reset_rank_value_of_item',params).then(function(result){
if (result.isResultsOK())
{
console.log('Drag sort result saved successfully'); }}); }}}); resetMenuDiv(result.find('menu'));
});
});
</script>
Copy the codeAfter the language
Why is a basic user ranking function so difficult? Although the question was four or five years ago, I remembered that I did this research and haven’t updated my blog for a long time, so I have this article to share for your reference.
From the original star blog: wanyaxing.com/blog/201907…