what's the most efficient way to manage record position in a RDBMS table
0
let's say we have records in a table and we would like to be able to order (and reorder them).
the table could look like something like this, see below
id Pos Level parentId
Europe 18 1 0 null
Germany 9 2 1 18
Berlin 2 3 2 9
Frankfurt 20 4 2 9
Stuttgart 23 5 2 9
France 29 6 1 18
Paris 26 7 2 29
Lyon 13 8 2 29
Americas 11 9 0 null
USA 27 10 1 11
New York 22 11 2 27
Manhattan 19 12 3 22
Brooklyn 7 13 3 22
Los Angeles 25 14 2 27
Mexico 6 15 1 11
Canada 4 16 1 11
Montreal 21 17 2 4
Vancouver 3 18 2 4
Asia 8 19 0 null
China 14 20 1 8
Beijing 17 21 2 14
Shenzhen 30 22 2 14
Shanghai 28 23 2 14
Japan 16 24 1 8
Tokyo 1 25 2 16
Shinjuku 15 26 3 1
Oceania 24 27 0 null
Autralia 5 28 1 24
Sydney 10 29 2 5
Africa
12 30 0 null
where id is a unique id (can be anything), position the position of the element in the list, level depth level and parentId parent id (if exists)
Typically I would want the following method:
/**
@param sourceId: id of the element to be moved
@params targetId: id of the element which position needs to be overtaken
@param aboveOrBelow: defines whether the old element (target) will be placed above or below the source element
@return if successful, new position of the source element, if unsuccessful: message explaining why unsuccessful
*/
def move(sourceId: Long, targetId: Long, aboveOrBelow: Boolean = true):Either[Long, String]
what's the most efficient way to implement this or am I missing something? Is there already a built-in mechanism for such operations in (My)SQL?
constraints:
- end user who might be allowed to reorder do not necessarily see all records (e.g. only asian records)
- records can be added and deleted
=== edit ===
i rewrote the structure taking in account suggestions in the comments:
id pos parentId
Europe 18 1 null
Germany 9 1 18
Berlin 2 1 9
Frankfurt 20 2 9
Stuttgart 23 3 9
France 29 2 18
Paris 26 1 29
Lyon 13 2 29
Americas 11 2 null
USA 27 1 11
New York 22 1 27
Manhattan 19 1 22
Brooklyn 7 2 22
Los Angeles 25 2 27
Mexico 6 3 11
Canada 4 4 11
Montreal 21 1 4
Vancouver 3 2 4
Asia 8 3 null
China 14 1 8
Beijing 17 1 14
Shenzhen 30 2 14
Shanghai 28 3 14
Japan 16 2 8
Tokyo 1 1 16
Shinjuku 15 1 1
Oceania 24 4 null
Autralia 5 1 24
Sydney 10 1 5
Africa 12 5 null
mysql sql rdbms
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
add a comment |
0
let's say we have records in a table and we would like to be able to order (and reorder them).
the table could look like something like this, see below
id Pos Level parentId
Europe 18 1 0 null
Germany 9 2 1 18
Berlin 2 3 2 9
Frankfurt 20 4 2 9
Stuttgart 23 5 2 9
France 29 6 1 18
Paris 26 7 2 29
Lyon 13 8 2 29
Americas 11 9 0 null
USA 27 10 1 11
New York 22 11 2 27
Manhattan 19 12 3 22
Brooklyn 7 13 3 22
Los Angeles 25 14 2 27
Mexico 6 15 1 11
Canada 4 16 1 11
Montreal 21 17 2 4
Vancouver 3 18 2 4
Asia 8 19 0 null
China 14 20 1 8
Beijing 17 21 2 14
Shenzhen 30 22 2 14
Shanghai 28 23 2 14
Japan 16 24 1 8
Tokyo 1 25 2 16
Shinjuku 15 26 3 1
Oceania 24 27 0 null
Autralia 5 28 1 24
Sydney 10 29 2 5
Africa
12 30 0 null
where id is a unique id (can be anything), position the position of the element in the list, level depth level and parentId parent id (if exists)
Typically I would want the following method:
/**
@param sourceId: id of the element to be moved
@params targetId: id of the element which position needs to be overtaken
@param aboveOrBelow: defines whether the old element (target) will be placed above or below the source element
@return if successful, new position of the source element, if unsuccessful: message explaining why unsuccessful
*/
def move(sourceId: Long, targetId: Long, aboveOrBelow: Boolean = true):Either[Long, String]
what's the most efficient way to implement this or am I missing something? Is there already a built-in mechanism for such operations in (My)SQL?
constraints:
- end user who might be allowed to reorder do not necessarily see all records (e.g. only asian records)
- records can be added and deleted
=== edit ===
i rewrote the structure taking in account suggestions in the comments:
id pos parentId
Europe 18 1 null
Germany 9 1 18
Berlin 2 1 9
Frankfurt 20 2 9
Stuttgart 23 3 9
France 29 2 18
Paris 26 1 29
Lyon 13 2 29
Americas 11 2 null
USA 27 1 11
New York 22 1 27
Manhattan 19 1 22
Brooklyn 7 2 22
Los Angeles 25 2 27
Mexico 6 3 11
Canada 4 4 11
Montreal 21 1 4
Vancouver 3 2 4
Asia 8 3 null
China 14 1 8
Beijing 17 1 14
Shenzhen 30 2 14
Shanghai 28 3 14
Japan 16 2 8
Tokyo 1 1 16
Shinjuku 15 1 1
Oceania 24 4 null
Autralia 5 1 24
Sydney 10 1 5
Africa 12 5 null
mysql sql rdbms
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
1
do you really need the level column? It's implicit by looking at the parent(s). And perhaps "pos" needs to be only within the items who share the same immediate parent - their position overall is inevitably determined by their parent, after all. I think that would make your data structure more efficient and probably make your queries a bit less complex to implement. It also would match more closely with the scenario you mentioned where the person doing the re-ordering can only see a subset of the data.
– ADyson
Nov 20 '18 at 13:06
I think you need to clarify whether this is specific to MySQL or any RDBMS. Oracle, for example, has "connect by) and others use CTE (common table expressions) but MySQL really has no equivalent. Your best bet is to use a temp table to contain the reordering and then either delete and reinsert or update the existing rows after your operations are complete. One more thing: what version of MySQL are you using? The poster below is correct if MySQL 8 or above.
– T Gray
Nov 20 '18 at 17:10
add a comment |
0
0
0
let's say we have records in a table and we would like to be able to order (and reorder them).
the table could look like something like this, see below
id Pos Level parentId
Europe 18 1 0 null
Germany 9 2 1 18
Berlin 2 3 2 9
Frankfurt 20 4 2 9
Stuttgart 23 5 2 9
France 29 6 1 18
Paris 26 7 2 29
Lyon 13 8 2 29
Americas 11 9 0 null
USA 27 10 1 11
New York 22 11 2 27
Manhattan 19 12 3 22
Brooklyn 7 13 3 22
Los Angeles 25 14 2 27
Mexico 6 15 1 11
Canada 4 16 1 11
Montreal 21 17 2 4
Vancouver 3 18 2 4
Asia 8 19 0 null
China 14 20 1 8
Beijing 17 21 2 14
Shenzhen 30 22 2 14
Shanghai 28 23 2 14
Japan 16 24 1 8
Tokyo 1 25 2 16
Shinjuku 15 26 3 1
Oceania 24 27 0 null
Autralia 5 28 1 24
Sydney 10 29 2 5
Africa
12 30 0 null
where id is a unique id (can be anything), position the position of the element in the list, level depth level and parentId parent id (if exists)
Typically I would want the following method:
/**
@param sourceId: id of the element to be moved
@params targetId: id of the element which position needs to be overtaken
@param aboveOrBelow: defines whether the old element (target) will be placed above or below the source element
@return if successful, new position of the source element, if unsuccessful: message explaining why unsuccessful
*/
def move(sourceId: Long, targetId: Long, aboveOrBelow: Boolean = true):Either[Long, String]
what's the most efficient way to implement this or am I missing something? Is there already a built-in mechanism for such operations in (My)SQL?
constraints:
- end user who might be allowed to reorder do not necessarily see all records (e.g. only asian records)
- records can be added and deleted
=== edit ===
i rewrote the structure taking in account suggestions in the comments:
id pos parentId
Europe 18 1 null
Germany 9 1 18
Berlin 2 1 9
Frankfurt 20 2 9
Stuttgart 23 3 9
France 29 2 18
Paris 26 1 29
Lyon 13 2 29
Americas 11 2 null
USA 27 1 11
New York 22 1 27
Manhattan 19 1 22
Brooklyn 7 2 22
Los Angeles 25 2 27
Mexico 6 3 11
Canada 4 4 11
Montreal 21 1 4
Vancouver 3 2 4
Asia 8 3 null
China 14 1 8
Beijing 17 1 14
Shenzhen 30 2 14
Shanghai 28 3 14
Japan 16 2 8
Tokyo 1 1 16
Shinjuku 15 1 1
Oceania 24 4 null
Autralia 5 1 24
Sydney 10 1 5
Africa 12 5 null
mysql sql rdbms
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
let's say we have records in a table and we would like to be able to order (and reorder them).
the table could look like something like this, see below
id Pos Level parentId
Europe 18 1 0 null
Germany 9 2 1 18
Berlin 2 3 2 9
Frankfurt 20 4 2 9
Stuttgart 23 5 2 9
France 29 6 1 18
Paris 26 7 2 29
Lyon 13 8 2 29
Americas 11 9 0 null
USA 27 10 1 11
New York 22 11 2 27
Manhattan 19 12 3 22
Brooklyn 7 13 3 22
Los Angeles 25 14 2 27
Mexico 6 15 1 11
Canada 4 16 1 11
Montreal 21 17 2 4
Vancouver 3 18 2 4
Asia 8 19 0 null
China 14 20 1 8
Beijing 17 21 2 14
Shenzhen 30 22 2 14
Shanghai 28 23 2 14
Japan 16 24 1 8
Tokyo 1 25 2 16
Shinjuku 15 26 3 1
Oceania 24 27 0 null
Autralia 5 28 1 24
Sydney 10 29 2 5
Africa
12 30 0 null
where id is a unique id (can be anything), position the position of the element in the list, level depth level and parentId parent id (if exists)
Typically I would want the following method:
/**
@param sourceId: id of the element to be moved
@params targetId: id of the element which position needs to be overtaken
@param aboveOrBelow: defines whether the old element (target) will be placed above or below the source element
@return if successful, new position of the source element, if unsuccessful: message explaining why unsuccessful
*/
def move(sourceId: Long, targetId: Long, aboveOrBelow: Boolean = true):Either[Long, String]
what's the most efficient way to implement this or am I missing something? Is there already a built-in mechanism for such operations in (My)SQL?
constraints:
- end user who might be allowed to reorder do not necessarily see all records (e.g. only asian records)
- records can be added and deleted
=== edit ===
i rewrote the structure taking in account suggestions in the comments:
id pos parentId
Europe 18 1 null
Germany 9 1 18
Berlin 2 1 9
Frankfurt 20 2 9
Stuttgart 23 3 9
France 29 2 18
Paris 26 1 29
Lyon 13 2 29
Americas 11 2 null
USA 27 1 11
New York 22 1 27
Manhattan 19 1 22
Brooklyn 7 2 22
Los Angeles 25 2 27
Mexico 6 3 11
Canada 4 4 11
Montreal 21 1 4
Vancouver 3 2 4
Asia 8 3 null
China 14 1 8
Beijing 17 1 14
Shenzhen 30 2 14
Shanghai 28 3 14
Japan 16 2 8
Tokyo 1 1 16
Shinjuku 15 1 1
Oceania 24 4 null
Autralia 5 1 24
Sydney 10 1 5
Africa 12 5 null
mysql sql rdbms
mysql sql rdbms
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
edited Nov 20 '18 at 13:13
John
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
asked Nov 20 '18 at 13:01
JohnJohn
1,4021727
1,4021727
1
do you really need the level column? It's implicit by looking at the parent(s). And perhaps "pos" needs to be only within the items who share the same immediate parent - their position overall is inevitably determined by their parent, after all. I think that would make your data structure more efficient and probably make your queries a bit less complex to implement. It also would match more closely with the scenario you mentioned where the person doing the re-ordering can only see a subset of the data.
– ADyson
Nov 20 '18 at 13:06
I think you need to clarify whether this is specific to MySQL or any RDBMS. Oracle, for example, has "connect by) and others use CTE (common table expressions) but MySQL really has no equivalent. Your best bet is to use a temp table to contain the reordering and then either delete and reinsert or update the existing rows after your operations are complete. One more thing: what version of MySQL are you using? The poster below is correct if MySQL 8 or above.
– T Gray
Nov 20 '18 at 17:10
add a comment |
1
do you really need the level column? It's implicit by looking at the parent(s). And perhaps "pos" needs to be only within the items who share the same immediate parent - their position overall is inevitably determined by their parent, after all. I think that would make your data structure more efficient and probably make your queries a bit less complex to implement. It also would match more closely with the scenario you mentioned where the person doing the re-ordering can only see a subset of the data.
– ADyson
Nov 20 '18 at 13:06
I think you need to clarify whether this is specific to MySQL or any RDBMS. Oracle, for example, has "connect by) and others use CTE (common table expressions) but MySQL really has no equivalent. Your best bet is to use a temp table to contain the reordering and then either delete and reinsert or update the existing rows after your operations are complete. One more thing: what version of MySQL are you using? The poster below is correct if MySQL 8 or above.
– T Gray
Nov 20 '18 at 17:10
1
1
do you really need the level column? It's implicit by looking at the parent(s). And perhaps "pos" needs to be only within the items who share the same immediate parent - their position overall is inevitably determined by their parent, after all. I think that would make your data structure more efficient and probably make your queries a bit less complex to implement. It also would match more closely with the scenario you mentioned where the person doing the re-ordering can only see a subset of the data.
– ADyson
Nov 20 '18 at 13:06
do you really need the level column? It's implicit by looking at the parent(s). And perhaps "pos" needs to be only within the items who share the same immediate parent - their position overall is inevitably determined by their parent, after all. I think that would make your data structure more efficient and probably make your queries a bit less complex to implement. It also would match more closely with the scenario you mentioned where the person doing the re-ordering can only see a subset of the data.
– ADyson
Nov 20 '18 at 13:06
I think you need to clarify whether this is specific to MySQL or any RDBMS. Oracle, for example, has "connect by) and others use CTE (common table expressions) but MySQL really has no equivalent. Your best bet is to use a temp table to contain the reordering and then either delete and reinsert or update the existing rows after your operations are complete. One more thing: what version of MySQL are you using? The poster below is correct if MySQL 8 or above.
– T Gray
Nov 20 '18 at 17:10
I think you need to clarify whether this is specific to MySQL or any RDBMS. Oracle, for example, has "connect by) and others use CTE (common table expressions) but MySQL really has no equivalent. Your best bet is to use a temp table to contain the reordering and then either delete and reinsert or update the existing rows after your operations are complete. One more thing: what version of MySQL are you using? The poster below is correct if MySQL 8 or above.
– T Gray
Nov 20 '18 at 17:10
add a comment |
1 Answer
1
active
oldest
votes
1
And for constructing the tree structure i would use recursive cte as follows. and construct it as a view
with recursive cte(place_name,id,parent_id,level)
as (select place_name,id,parent_id,1 as level
from countries_hierarchy
where parent_id is null
union all
select concat(lpad(' ',a.level+1,' ')
,b.place_name
)
,b.id
,b.parent_id
,a.level+1
from cte a
join countries_hierarchy b
on a.id=b.parent_id
)
select * from cte
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=334820e4e01cf8749c5abcaa447963a0
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393578%2fwhats-the-most-efficient-way-to-manage-record-position-in-a-rdbms-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
And for constructing the tree structure i would use recursive cte as follows. and construct it as a view
with recursive cte(place_name,id,parent_id,level)
as (select place_name,id,parent_id,1 as level
from countries_hierarchy
where parent_id is null
union all
select concat(lpad(' ',a.level+1,' ')
,b.place_name
)
,b.id
,b.parent_id
,a.level+1
from cte a
join countries_hierarchy b
on a.id=b.parent_id
)
select * from cte
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=334820e4e01cf8749c5abcaa447963a0
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
add a comment |
1
And for constructing the tree structure i would use recursive cte as follows. and construct it as a view
with recursive cte(place_name,id,parent_id,level)
as (select place_name,id,parent_id,1 as level
from countries_hierarchy
where parent_id is null
union all
select concat(lpad(' ',a.level+1,' ')
,b.place_name
)
,b.id
,b.parent_id
,a.level+1
from cte a
join countries_hierarchy b
on a.id=b.parent_id
)
select * from cte
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=334820e4e01cf8749c5abcaa447963a0
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
add a comment |
1
1
1
And for constructing the tree structure i would use recursive cte as follows. and construct it as a view
with recursive cte(place_name,id,parent_id,level)
as (select place_name,id,parent_id,1 as level
from countries_hierarchy
where parent_id is null
union all
select concat(lpad(' ',a.level+1,' ')
,b.place_name
)
,b.id
,b.parent_id
,a.level+1
from cte a
join countries_hierarchy b
on a.id=b.parent_id
)
select * from cte
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=334820e4e01cf8749c5abcaa447963a0
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
And for constructing the tree structure i would use recursive cte as follows. and construct it as a view
with recursive cte(place_name,id,parent_id,level)
as (select place_name,id,parent_id,1 as level
from countries_hierarchy
where parent_id is null
union all
select concat(lpad(' ',a.level+1,' ')
,b.place_name
)
,b.id
,b.parent_id
,a.level+1
from cte a
join countries_hierarchy b
on a.id=b.parent_id
)
select * from cte
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=334820e4e01cf8749c5abcaa447963a0
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
answered Nov 20 '18 at 13:32
George JosephGeorge Joseph
1,59059
1,59059
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53393578%2fwhats-the-most-efficient-way-to-manage-record-position-in-a-rdbms-table%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
do you really need the level column? It's implicit by looking at the parent(s). And perhaps "pos" needs to be only within the items who share the same immediate parent - their position overall is inevitably determined by their parent, after all. I think that would make your data structure more efficient and probably make your queries a bit less complex to implement. It also would match more closely with the scenario you mentioned where the person doing the re-ordering can only see a subset of the data.
– ADyson
Nov 20 '18 at 13:06
I think you need to clarify whether this is specific to MySQL or any RDBMS. Oracle, for example, has "connect by) and others use CTE (common table expressions) but MySQL really has no equivalent. Your best bet is to use a temp table to contain the reordering and then either delete and reinsert or update the existing rows after your operations are complete. One more thing: what version of MySQL are you using? The poster below is correct if MySQL 8 or above.
– T Gray
Nov 20 '18 at 17:10