hallindavid's avatar

Question - what's the most efficient way to update a database with new sort orders?

Hi All - had a situation come up, where the user has a list present in the front end which is sortable via click and drag.

Now what the backend receives from this is something that looks like this

[
	[
		'order'=>1,
		'value'=>'id of first item',
	],
	[
		'order'=>2,
		'value'=>'id of second item',
	],
	...
]

So - My question comes around the most efficient way of doing this, because these lists can actually be up to around 100 items in some cases.

The default way, which is making me uncomfortable is something like this

foreach($new_order as $li) {
	\App\Models\Item::find($li['value'])
           ->update(['sort_order'=>$li['order']]);
}

because that seems like every time the user triggers a re-sort, then the there will be 100 database updates - which may be easy... but it feels like that's... not great.

The second way which takes more of the database work into the front-end.. which can have it's own problems is this - keep in mind I already have the items in the controller at this point through a

$items = $list->items()->get();

foreach($new_order as $li) {
  if ($items->firstWhere('id', $li['value'])->sort_order !== $li['order']) {
    \App\Models\Item::find($li['value'])->update(['sort_order'=>$li['order']]);
  }
}

The second one requires that PHP do some comparison work - which I feel like usually isn't great, but doing 100+ db queries every second where some of the values will be updating nothing also seems like a bad call.

What do you guys think?

0 likes
6 replies
hallindavid's avatar

You have no idea how awesome your answer was here.

1 like
hallindavid's avatar

Ok - for anybody who is wondering, there are a few hurtles here in the database.

The Upsert Method used an Insert query, which actually get's error-checked before executing the query.

The hiccup that I ran into was this.

$team_id = auth()->user()->currentTeam->id; //using jetstream w/ teams

Item::upsert(collect($new_order)->map(function($item) use($team_id) {
  return [
    'id'=>$item['order'],
    'team_id'=>$team_id,
    'sort_order'=>$item['order'],
  ];
})->toArray(), ['id','team_id'], ['sort_order']);

This is the first query I tried, and it failed because it kept saying something along the lines of

The column 'name' has no default value and isn't nullable.

This is because when you create an Item in the application it requires a name (by design).

The way that Upsert works behind the scenes is

INSERT into items (id, team_id, sort_order, created_at, updated_at) VALUES 
    ('id_for_item_1', 1, '2020-12-10 13:30:00', '2020-12-10 13:30:00'), 
    ('id_for_item_2', 2, '2020-12-10 13:30:00', '2020-12-10 13:30:00'), 
    ('id_for_item_3', 3, '2020-12-10 13:30:00', '2020-12-10 13:30:00') 
on duplicate key
update sort_order = values(sort_order), updated_at = values(updated_at)

When it does this - it actually checks to make sure the insert statement is valid before executing, so all your non-nullables are going to break the insert statement.

So, one reasonable solution, if you are positive that the items will not be deleted from the database between being displayed and updated, you can set blank values for all your non-nullable columns.

$team_id = auth()->user()->currentTeam->id; //using jetstream w/ teams

Item::upsert(collect($new_order)->map(function($item) use($team_id) {
  return [
    'id'=>$item['order'],
    'name'=>'',
    'due_date'=>now(),
    'team_id'=>$team_id,
    'sort_order'=>$item['order'],
  ];
})->toArray(), ['id','team_id'], ['sort_order']);

This should be safe because when it does the insert using the id field (assuming it's the primary key) it should always flag as a duplicate, and therefore always update.

2 likes

Please or to participate in this conversation.