0

I have to do a list in php that can be rearranged by the user. This is what I came up with:

Table layout:

ID | Name      | previous
1  | "first"   | NULL
2  | "second"  | 1
3  | "third"   | 2
4  | "fourth"  | 3

Lets say I want to swap "third" with "second". The table would look like this after the query:

ID | Name      | previous
1  | "first"   | NULL
3  | "third"   | 1
2  | "second"  | 3
4  | "fourth"  | 2

So what happens?

1) "Fourth" gets "second"'s value (from 3 to 2).

2) "Third" gets "second"'s previous value (from 2 to 1).

3) "Second" get's "fourth"'s previous value (from 1 to 3... obviously there is a conflict with 1) if it's in that order).

What I need is a query that does what I just explained. I don't want to use multiple queries, so that's why I want to use subqueries instead.

I already have a SELECT statement that gives me all the data of the 3 rows to be changed... But how do I update the columns of each row?:

SELECT * FROM test a JOIN test b ON a.id = 3 AND b.id = ( SELECT id FROM test WHERE id < 3 ORDER BY id DESC limit 1 ) JOIN test c ON a.id = 3 AND c.id = ( SELECT id FROM test WHERE id > 3 ORDER BY id ASC limit 1 )

Johannes
  • 1,249
  • 3
  • 17
  • 33
  • If you want to swap "second" and "third", why does the value of "fourth" change? – Gordon Linoff Aug 15 '14 at 12:45
  • it's great, recently I needed a query and I sat down for half an hour, drew some diagrams and wrote it myself. Can you show us what you've tried? – serakfalcon Aug 15 '14 at 12:46
  • if "third" is 'behind' "second", then "second" becomes the row behind "fourth". I hope that makes sense.... Let me put it in other words: If only "second" and "third" would change, then "third" would still be the precursor of "forth" – Johannes Aug 15 '14 at 12:46
  • why store 'previous' instead of the straight order? it will make things simpler. – serakfalcon Aug 15 '14 at 12:46
  • Do you know Nested Set? http://www.evanpetersen.com/item/nested-sets.html – Sebastian Brosch Aug 15 '14 at 12:48
  • @serakfalcon This is what I have so far: This query will select 2 rows (no idea how to do it with 3 yet), that I want to swap... With multiple queries, I could achieve what I want with 2 queries. The Update statement with this query is not working though... `SELECT * FROM test a JOIN test b ON a.id = 3 AND b.id = ( SELECT id FROM test WHERE id < 3 ORDER BY id DESC limit 1 )` – Johannes Aug 15 '14 at 13:00
  • @Johannes - that information should be in your question. Unless I miss my guess, you can do this via the simple expedient of defining an inline table (say, with `VALUES(...)`) that defines "origin" and "destination" row values. – Clockwork-Muse Aug 15 '14 at 13:04
  • @SebastianBrosch - Except that post points out that nested sets aren't great for update, usually because of the number of rows involved (may be the case here, unknown). @Johannes - do you need a strict numeric index, or just rough ordering? If you only need rough ordering, perhaps split the difference between the index of the neighbor entries, where you started out by inserting a `0`, halfway between there and `INTEGER.Max_Value`, etc...? – Clockwork-Muse Aug 15 '14 at 13:22
  • Dang, this would be a lot easier in something like PostgreSQL, with `LAG()`/`LEAD()` (or at least CTEs to only grab/calc certain things only once). Your `SELECT` statement won't actually get you correct results for subsequent re-orderings (ordering by an `id` is rarely relevant, even in these situations). You'd be better off defining a specific "order by me" column completely divorced from the id, and potentially not even strictly sequential (ie, leave gaps to facilitate movement). Or just grab all rows, re-order them in php, then update the db with the changes. – Clockwork-Muse Aug 15 '14 at 13:47

2 Answers2

2

There is no way to achieve that with exactly one query*.

However, you could use a SQL transaction (if you use InnoDB) - this way you have the guarantee that the data is consistent.

start transaction;
select * from table for update;
... do the updates here...
commit;

The for update keywords causes these rows to be locked (for other writing queries or for other for update queries) until the transaction ends (all other queries will see the state before the transation or the result after the whole transaction finished).

(*) You just need to update 4 rows (maximum). The two you want to exchange and two rows pointing to them as previous. BUT, in order to know which ones to update you need to do (one or two) select queries - and there you need a transaction to keep data consistent, because at least in MySQL, you cannot combine an update query with select subqueries on the same table.

MrTux
  • 32,350
  • 30
  • 109
  • 146
  • Sure you can do this in one query. Why do you think it can't? I'm also not sure why you feel the need to bring it into the application layer, as opposed to just using multiple `UPDATE` statements anyways... – Clockwork-Muse Aug 15 '14 at 13:06
  • The rows are dependant on each other in this case. – MrTux Aug 15 '14 at 13:10
  • Well, @Clockwork-Muse, you're kinda right. You just need to update 4 rows (maximum). The two you want to exchange and two rows pointing to them as previous. BUT, in order to know which ones to update you need to do (one or two) select queries - and there you need a transaction to keep data consistent, because at least in MySQL, you cannot combine an update query with select subqueries on the same table. – MrTux Aug 15 '14 at 13:13
  • So it's not possible to update all 3 rows within 1 query? So what I would be doing is swap the columns in the transaction for all according rows? – Johannes Aug 15 '14 at 13:18
  • You need at least three sql statements: Two select and one update. however, putting everything in one update query (using `case`) won't make it better readable. And to insure the data stays consistent you need to add `for update` for all your select queries and put all queries into a transaction. – MrTux Aug 15 '14 at 13:26
0

You are probably better off doing multiple updates wrapped in a transaction. It's the cleanest code and it should be acceptably fast. If you insist though ...

Community
  • 1
  • 1
user2910265
  • 844
  • 1
  • 9
  • 15