Mysql - PHP: Re-sorting Display Order with Selected Rows and Inputted Starting Point Value

HeyIt39sCheff
December 12, 2023
150 views
1 vote
2 Answers

Just attended a concluding interview where the interviewer assigned me a task involving CRUD operations. The task specifically entails displaying data sorted by the "display_order" column. Within the table list, there is a checkbox that allows users to rearrange the order. To do so, one must check the box and specify a starting point for the display order. For instance:

Example:
Starting point: 2
Original List:

id	display order	checkbox
69	1
71	2
37	3
52	4
59	5
60	6
86	7	checked
84	8
77	9	checked
44	10	checked

Expected Result:

id	display order	checkbox
69	1
86	2
77	3
44	4
71	5
37	6
52	7
59	8
60	9
84	10

The Result:

[
  {
    "id": "69",
    "sort": 1
  },
  {
    "id": "86",
    "sort": "2"
  },
  {
    "id": "77",
    "sort": "3"
  },
  {
    "id": "44",
    "sort": "4"
  },
  {
    "id": "52",
    "sort": 5
  },
  {
    "id": "71",
    "sort": 5
  },
  {
    "id": "37",
    "sort": 5
  },
  {
    "id": "59",
    "sort": "5"
  },
  {
    "id": "60",
    "sort": "6"
  },
  {
    "id": "84",
    "sort": "8"
  }
]

Another Example:
Starting point: 8

Expected Result:

id	display order	checkbox
69	1
71	2
37	3
52	4
59	5
60	6
84	7
86	8
77	9
44	10

The Result:

[
  {
    "id": "69",
    "sort": 1
  },
  {
    "id": "71",
    "sort": 2
  },
  {
    "id": "37",
    "sort": 3
  },
  {
    "id": "52",
    "sort": 4
  },
  {
    "id": "59",
    "sort": 5
  },
  {
    "id": "60",
    "sort": 6
  },
  {
    "id": "86",
    "sort": "8"
  },
  {
    "id": "77",
    "sort": "9"
  },
  {
    "id": "44",
    "sort": "10"
  },
  {
    "id": "84",
    "sort": 11
  }
]

Here’s what I have so far for the code:

public function reorder_list($request, $model = 'talk_news') {
    $inputted_display_order = 7;

    if (isset($request['sortable']) && $request['sortable']) {
        $this->setOrm(ORM::for_table($model));
        $sorted_ids = array_column($request['sortable'], 'id');
        $sorted_display_order = array_column($request['sortable'], 'sort');
        $first_display_order = reset($sorted_display_order);
        $last_display_order = end($sorted_display_order);
        $new_order = [];

        $query = $this->_orm->select('id')
            ->select('display_order')
            ->where_not_in('id', $sorted_ids)
            ->order_by_asc('display_order')
            ->find_array();

        $starting_display_order = $first_display_order == 1 ? count($sorted_display_order) + 1 : 1;

        foreach($query as $item) {
            $id = $item['id'];
            $display_order = $item['display_order'];

            $sort_value = $starting_display_order++;

            if ($display_order > $last_display_order && $first_display_order != 1) {
                $sort_value = $display_order;
            }

            if($display_order >= min($sorted_display_order) && $display_order <= max($sorted_display_order) && $first_display_order != 1) {
                $count_to_max = (max($sorted_display_order) + 1) - $display_order;
                $sort_value = $display_order + $count_to_max;
            }

            $new_order[] = [
                'id'   => $id,
                'sort' => $sort_value
            ];
        }

        $new_order = array_merge($new_order, $request['sortable']);

        usort($new_order, function ($a, $b) {
            return $a['sort'] - $b['sort'];
        });

        return $new_order;
    }
}

To replicate this, I’ll provide the data from the query and for the $request[‘sortable’]:

Sample Data for $query:

[{"id":"69","display_order":"1"},{"id":"71","display_order":"2"},{"id":"37","display_order":"3"},{"id":"52","display_order":"4"},{"id":"59","display_order":"5"},{"id":"60","display_order":"6"},{"id":"84","display_order":"8"},{"id":"74","display_order":"11"},{"id":"64","display_order":"12"},{"id":"25","display_order":"13"},{"id":"70","display_order":"14"},{"id":"73","display_order":"15"},{"id":"68","display_order":"16"},{"id":"38","display_order":"17"},{"id":"26","display_order":"18"},{"id":"5","display_order":"19"},{"id":"33","display_order":"20"},{"id":"41","display_order":"21"},{"id":"40","display_order":"22"},{"id":"55","display_order":"23"},{"id":"57","display_order":"24"},{"id":"61","display_order":"25"},{"id":"65","display_order":"26"},{"id":"66","display_order":"27"},{"id":"67","display_order":"28"},{"id":"46","display_order":"29"},{"id":"76","display_order":"30"},{"id":"78","display_order":"31"},{"id":"79","display_order":"32"},{"id":"81","display_order":"33"}]

Sample data for $request[‘sortable’]

[{"id":"86","sort":"2"},{"id":"77","sort":"3"},{"id":"44","sort":"4"}]

Answers

public function reorder_list($request, $model = 'talk_news') {
    $inputted_display_order = 7;
    if (isset($request['sortable']) && $request['sortable']) {
        $this->setOrm(ORM::for_table($model));
        $sorted_ids = array_column($request['sortable'], 'id');
        $sorted_display_order = array_column($request['sortable'], 'sort');
        $starting_display_order = max($sorted_display_order) + 1;
        $remaining_items = $this->_orm
            ->select('id')
            ->select('display_order')
            ->where_not_in('id', $sorted_ids)
            ->order_by_asc('display_order')
            ->find_array();
        $new_order = [];

        foreach ($remaining_items as $item) {
            if ($item['display_order'] < $inputted_display_order) {
                $new_order[] = [
                    'id'   => $item['id'],
                    'sort' => $item['display_order']
                ];
            }
        }
        foreach ($request['sortable'] as $item) {
            $new_order[] = [
                'id'   => $item['id'],
                'sort' => $starting_display_order++
            ];
        }
        foreach ($remaining_items as $item) {
            if ($item['display_order'] >= $inputted_display_order) {
                $new_order[] = [
                    'id'   => $item['id'],
                    'sort' => $starting_display_order++
                ];
            }
        }
        usort($new_order, function ($a, $b) {
            return $a['sort'] - $b['sort'];
        });

        return $new_order;
    }
}

You could re-index $request['sortable'] so that you have an array of ids indexed by sort. Then, for each display order take the id from the re-indexed array if it exists, else array_shift() next item from beginning of $query.

Something like:

public function reorder_list($request, $model = 'talk_news') {

    if (isset($request['sortable']) && $request['sortable']) {
        $this->setOrm(ORM::for_table($model));
        $sorted_ids = array_column($request['sortable'], 'id');

        /* create array of ids indexed by sort */
        $sorted_display_order = array_column($request['sortable'], 'id', 'sort');

        $new_order = [];

        $query = $this->_orm->select('id')
            ->select('display_order')
            ->where_not_in('id', $sorted_ids)
            ->order_by_asc('display_order')
            ->find_array();

        $total_count = count($query) + count($sorted_ids);

        for ($i = 1; $i <= $total_count; $i++) {
            if (array_key_exists($i, $sorted_display_order)) {
                $id = $sorted_display_order[$i];
            } else {
                $id = array_shift($query)['id'];
            }
            $new_order[] = ['id' => $id, 'sort' => $i];
        }

        return $new_order;
    }
}

Please signup or login to give your own answer.

Click here to cancel reply.

Mysql – PHP: Re-sorting Display Order with Selected Rows and Inputted Starting Point Value

Answers