StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POPostgreSQL, drag and swap
primarykey
Id
19458971
data
AcceptedAnswerId
19459864
AnswerCount
2
ClosedDate
CommentCount
7
CommunityOwnedDate
CreationDate
2013-10-18T21:00:46.757
FavoriteCount
0
LastActivityDate
2013-10-19T20:19:53.567
LastEditDate
2017-05-23T10:31:46.983
LastEditorUserId
-1
OwnerUserId
973238
ParentId
0
PostTypeId
1
Score
0
ViewCount
190
LastEditorDisplayName
text
Body
I have successfully solved situation regarding swapping rows by value <a href="https://stackoverflow.com/questions/18250750/postgresql-advanced-swapping">here</a> what still work excellent. But by using this function I see some lacks of functionality in mean of drag and drop rows. I try to get solution <a href="https://stackoverflow.com/questions/14085258/how-do-i-get-a-column-with-consecutive-increasing-numbers-without-having-any-n/14092775#14092775">here</a> where discussion goes wrong way and offered solution is not adequate since they need additional columns. Here is well known table: <pre><code>DROP TABLE IF EXISTS kalksad1; CREATE TABLE kalksad1( kalk_id int PRIMARY KEY, brkalk integer, brred integer, description text); INSERT INTO kalksad1 VALUES (12, 2, 5, 'text index 12 doc 2 row 5'), (26, 2, 1, 'text index 26 doc 2 row 1'), (30, 2, 2, 'text index 30 doc 2 row 2'), (32, 4, 1, 'text index 32 doc 4 row 1'), (36, 1, 1, 'text index 36 doc 1 row 1'), (37, 1, 2, 'text index 37 doc 1 row 2'), (38, 5, 1, 'text index 38 doc 5 row 1'), (39, 5, 2, 'text index 39 doc 5 row 2'), (42, 2, 3, 'text index 42 doc 2 row 3'), (43, 2, 4, 'text index 43 doc 2 row 4'), (46, 3, 1, 'text index 46 doc 3 row 1'), (47, 3, 2, 'text index 47 doc 3 row 2'); </code></pre> Object of manipulation is reordering values of column "brred"(row) under same "brkalk"(doc). Let "brkalk" be 2. Now I would like to get reordering/swapping according to drag and drop needs where swapping just one row look unnatural. I have datagrid binded to kalksad1 table so I will describe situation looking in my datagrid filled with query "... ORDERED by brred". If I could explain query by words that would be... Example 1: Under doc 2 I will drag row 4 and drop it to position of row 2. For that is needed following steps: 1) Remember data of row 4. 2) In row 3 replace value of "brred" from 3 to 4. 3) In row 2 replace value of "brred" from 2 to 3. 4) In remembered data from step 1) change value of "brred" from 4 to 2. Example 2: Under doc 2 I will drag row 1 and drop it to position 3. That can go like this: 1) Remember data of row 1. 2) In row 2 replace value of "brred" from 2 to 1. 3) In row 3 replace value of "brred" from 3 to 2. 4) In remembered data from step 1. change value of "brred" from 1 to 3. Idea that this may be possible comes from elegant solution with swapping and SO questions <a href="https://stackoverflow.com/questions/6037045/resequencing-a-column-with-identifier-in-postgresql">like this</a>, <a href="https://stackoverflow.com/questions/6725783/postgresql-record-reordering-using-update-with-a-sub-select">this</a> and <a href="https://stackoverflow.com/questions/3397121/how-to-show-row-numbers-in-postgresql-query">this</a>. I make examples based on my thinkings but that shouldn't go that way if better exist. Please if someone can write described query at way similar to those for <a href="https://stackoverflow.com/questions/18250750/postgresql-advanced-swapping">swapping</a> of user Roman Pekar. <pre><code>EDIT: Solution based on Example1 from Tometzky Imports Npgsql Public Class Form1 Dim dServer As String = "127.0.0.1" Dim dPort As String = "5432" Dim dUser As String = "postgres" Dim dPass As String = yourpass Dim ddatabase As String = yourdatabase Private dragrect As Rectangle Private dragindex, dropindex As Integer Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim conn As New NpgsqlConnection(String.Format( _ "Server={0};Port={1};User Id={2};Password={3};Database={4};", _ dServer, dPort, dUser, dPass, ddatabase)) conn.Open() Using t As NpgsqlTransaction = conn.BeginTransaction() Using cmd As New NpgsqlCommand( _ "DROP TABLE IF EXISTS kalksad1;", conn) cmd.ExecuteNonQuery() End Using Using cmd As New NpgsqlCommand( _ "CREATE TABLE kalksad1(" & _ "kalk_id int PRIMARY KEY, " & _ "brkalk integer, " & _ "brred integer, " & _ "description text);", conn) cmd.ExecuteScalar() End Using Using cmd As New NpgsqlCommand( _ "INSERT INTO kalksad1 VALUES" & _ "(12, 2, 5, 'text index 12 doc 2 row 5'), " & _ "(26, 2, 1, 'text index 26 doc 2 row 1'), " & _ "(30, 2, 2, 'text index 30 doc 2 row 2'), " & _ "(32, 4, 1, 'text index 32 doc 4 row 1'), " & _ "(36, 1, 1, 'text index 36 doc 1 row 1'), " & _ "(37, 1, 2, 'text index 37 doc 1 row 2'), " & _ "(38, 5, 1, 'text index 38 doc 5 row 1'), " & _ "(39, 5, 2, 'text index 39 doc 5 row 2'), " & _ "(42, 2, 3, 'text index 42 doc 2 row 3'), " & _ "(43, 2, 4, 'text index 43 doc 2 row 4'), " & _ "(46, 3, 1, 'text index 46 doc 3 row 1'), " & _ "(47, 3, 2, 'text index 47 doc 3 row 2');", conn) cmd.ExecuteNonQuery() End Using t.Commit() End Using With DataGridView1 .AllowDrop = True .MultiSelect = False .Dock = DockStyle.Fill .SelectionMode = DataGridViewSelectionMode.FullRowSelect .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells .Columns.Add("col1", "ID") .Columns.Add("col2", "Doc") .Columns.Add("col3", "Row") .Columns.Add("col4", "Description") End With FillData(0) End Sub Private Sub FillData(ByVal dropindex As Integer) DataGridView1.Rows.Clear() Try Using mCon As New NpgsqlConnection(String.Format( _ "Server={0};Port={1};User Id={2};Password={3};Database={4};", _ dServer, dPort, dUser, dPass, ddatabase)) mCon.Open() Using mCmd = New NpgsqlCommand( _ "SELECT kalk_id, brkalk, brred, description " & _ "FROM kalksad1 " & _ "WHERE brkalk='2' ORDER BY brred", mCon) Using reader As NpgsqlDataReader = mCmd.ExecuteReader() While (reader.Read()) DataGridView1.Rows.Add(New String() _ {CStr(reader("kalk_id")), _ CStr(reader("brkalk")), _ CStr(reader("brred")), _ CStr(reader("description"))}) End While End Using End Using End Using Catch ex As Exception Debug.Print(ex.Message) End Try ''selecting a row If dropindex < 0 Then dropindex = 0 With DataGridView1 .Rows(dropindex).Selected = True .CurrentCell = .Item(0, dropindex) End With End Sub #Region "dragdrop" Private Sub DataGridView1_DragDrop(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DataGridView1.DragDrop Dim p As Point = Me.PointToClient(New Point(e.X, e.Y)) dropindex = DataGridView1.HitTest(p.X, p.Y).RowIndex If (e.Effect = DragDropEffects.Move) Then Dim dragRow As DataGridViewRow = CType(e.Data.GetData(GetType(DataGridViewRow)), DataGridViewRow) Dim _from As Integer = dragindex + 1 ''grid is zero based, document is 1 based Dim _to As Integer = dropindex + 1 Dim updown As String = "" If _from < _to Then ''correction for up _to = _to + 1 updown = "!" End If '' PROCEDURE HERE ----------------------------------------------------------------- Dim affected As Integer = 0 Try Using conn As New NpgsqlConnection(String.Format( _ "Server={0};Port={1};User Id={2};Password={3};Database={4};", _ dServer, dPort, dUser, dPass, ddatabase)) conn.Open() Using t As NpgsqlTransaction = conn.BeginTransaction() Using cmd As New NpgsqlCommand( _ "UPDATE kalksad1 SET brred=_brred " & _ "FROM (" & _ " SELECT " & _ " row_number() OVER (" & _ " ORDER BY brred<" & _to.ToString & " DESC, brred" & updown & "=" & _from.ToString & " DESC, brred>=" & _to.ToString & " DESC, brred" & _ " ) AS _brred," & _ " kalk_id AS _kalk_id " & _ "FROM kalksad1 " & _ "WHERE brkalk=2 " & _ "ORDER BY _kalk_id" & _ ") AS _ " & _ "WHERE kalk_id=_kalk_id AND brred!=_brred;", conn) affected = CInt(cmd.ExecuteNonQuery()) End Using If affected > 0 Then t.Commit() End Using End Using Catch ex As Exception Debug.Print(ex.Message) End Try ''--------------------------------------------------------------------------------- FillData(dropindex) ''clear, fill and select dropped row End If End Sub Private Sub DataGridView1_DragOver(ByVal sender As Object, ByVal e As System.Windows.Forms.DragEventArgs) Handles DataGridView1.DragOver e.Effect = DragDropEffects.Move End Sub Private Sub DataGridView1_MouseDown(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseDown dragindex = DataGridView1.HitTest(e.X, e.Y).RowIndex If dragindex > -1 Then Dim dragSize As Size = SystemInformation.DragSize dragrect = New Rectangle(New Point(CInt(e.X - (dragSize.Width / 2)), CInt(e.Y - (dragSize.Height / 2))), dragSize) Else dragrect = Rectangle.Empty End If End Sub Private Sub DataGridView1_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles DataGridView1.MouseMove If (e.Button And MouseButtons.Left) = MouseButtons.Left Then If (dragrect <> Rectangle.Empty AndAlso Not dragrect.Contains(e.X, e.Y)) Then Me.DoDragDrop(DataGridView1.Rows(dragindex), DragDropEffects.Move) End If End If End Sub #End Region End Class </code></pre>
Tags
<postgresql>
Title
PostgreSQL, drag and swap
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USCommunity
UserOwnerUserId
1. USWine Too
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
2. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
3. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.