0

I have a playlist consisting of videos with positions.

In "delete selected video" section of my script, after deleting the selected video, I am updating the position of other videos on the playlist (stored on playlist_videos table), using the code below.

$playlist_videos = playlist_videos($playlist_id); // gets all videos on the given playlist, sorted by position

$i = 1;
foreach ($playlist_videos as $video) {
    DB::$db->query("UPDATE playlist_videos SET position = $i WHERE video = ".$video['id']." AND playlist = $playlist_id");
    $i++;
}

I currently do a foreach loop which results in multiple queries, and I am wondering if it is possible to turn it into one single query, to reduce DB load.

EDIT: A video can be on the same playlist multiple times, at different positions.

yenren
  • 450
  • 9
  • 20
  • Essentially, this assigns a random value to each item in the playlist – Strawberry Mar 05 '21 at 08:39
  • 1
    `I am updating the position of other videos on the playlist` ... this sounds like maybe the wrong approach. Deleting an element from the playlist should not affect the currently existing ranking in your table. – Tim Biegeleisen Mar 05 '21 at 08:39
  • @Strawberry, after deleting the selected video, say at position = 3, the remaining videos have positions 1, 2, 4, 5, 6, ... My code gets those videos in this order, and then assigns their new positions as 1, 2, 3, 4, 5, ... – yenren Mar 05 '21 at 09:06
  • @TimBiegeleisen Possibly mine is not the best approach but it was what worked so far. Irrelevant to my specific question above, a video can be on multiple playlists. Hence, when a video is removed, I am using a loop of playlists and then the above loop inside of each playlist. "Deleting an element from the playlist should not affect the currently existing ranking in your table." If only I could translate that into code. – yenren Mar 05 '21 at 09:08
  • As TB says, you don't need to store the absolute rank; the relative rank will suffice. – Strawberry Mar 05 '21 at 12:48
  • In a single SQL statement just set the position to (position - 1) where position > (position of deleted video) – NickW Mar 05 '21 at 13:53
  • @NickW, thanks for the suggestion, but in my system, a video can be on the same playlist multiple times, at different positions. Hence, your suggestion won't work as far as I can think of. – yenren Mar 07 '21 at 09:04
  • @Strawberry I remember considering "relative rank" approach during my decision process, but for some reason it didn't suit my needs. Though, I would be happy to see a working example if you know any. – yenren Mar 07 '21 at 09:05
  • If you know the record in the table which is/was deleted, why can't you just get its position (before deleting it) and then `UPDATE playlist_videos SET position = position - 1 WHERE playlist = XXX and position > YYY`? – MatBailie Mar 07 '21 at 09:16
  • Let's flip that and ask why you think it wouldn't suit your needs. – Strawberry Mar 07 '21 at 09:22
  • I just read your comment that you can't find the position of the deleted video, because a video can exist twice in the list. But, if you can identify the record to be deleted, you already Have found the position it has; you just need to read it before you delete it. If you can't do That, then fix That. – MatBailie Mar 07 '21 at 09:24
  • This can be done in a single update statement in MS SQL, I'm sure mysql has a row number function that you can use for this, not need to do it in the client at all – Chris Schaller Mar 07 '21 at 09:25
  • @ChrisSchaller Microsoft SQL Server does, MySQL 8 does, the still more common MySQL 5.x does not. The question is only tagged as MySQL (not MS SQL), so it's unclear if the op has `ROW_NUMBER()` or not. – MatBailie Mar 07 '21 at 09:33
  • See for instance https://stackoverflow.com/questions/65517250/creating-specific-size/65521552#65521552 -- if you delete, row 3, say, it still works, except you would now use the serial of the array, rather than the value, to identify the cardinal position. That said, I'm generally opposed to deleting data from databases (except where legally required to do so), so I would simply have a flag to record the visibility of rows, and think up some logic for what happens when a row gets dragged in front of (or behind) a hidden row. – Strawberry Mar 07 '21 at 11:34

1 Answers1

0

In v8.0 MySQL introduced the ROW_NUMBER() window function, this allows you to update to position in all affected playlists in a single query:

UPDATE playlist_videos AS p 
INNER JOIN (
    SELECT 
        position, video, playlist_id,
        ROW_NUMBER() OVER (PARTITION BY playlist_id ORDER BY position) AS rank
    FROM playlist_videos
) AS ranked ON p.position = ranked.position AND p.video = ranked.video AND p.playlist_id = ranked.playlist_id
SET p.Position = ranked.rank
WHERE p.Position <> ranked.rank

Simply adjust the where clause if you only want to update a specific playlist but this might be a useful query to resync the entire set.

If you don't have Version 8, then you can still do this in a single query, it just gets a bit more complicated, as described here ROW_NUMBER in MySQL:

UPDATE playlist_videos AS p
INNER JOIN (
  SELECT
    t.*, 
    @r := CASE 
      WHEN playlist_id = @prevcol THEN @r + 1 
      WHEN (@prevcol := playlist_id) = null THEN null
      ELSE 1 END AS rank 
  FROM
    playlist_videos as t, 
    (SELECT @r := 0, @prevcol := null) AS x
  ORDER BY playlist_id, position
) AS ranked ON p.position = ranked.position AND p.video = ranked.video AND p.playlist_id = ranked.playlist_id
SET p.Position = ranked.rank
WHERE p.Position <> ranked.rank;

I've setup a SQL Fiddle to play around with this, just uncomment the UPDATE statement in the schema panel, but the preview query is more than enough to prove the point: http://sqlfiddle.com/#!9/49c347/1

Chris Schaller
  • 13,704
  • 3
  • 43
  • 81
  • For the 5.x solution, technically, ORDER BY is in the wrong place, but I often wrote it this way too (before upgrading), with no ill-effects. That said, I think the solution is overkill. – Strawberry Mar 07 '21 at 11:22
  • Updating by rank is pretty standard, partioning by playlist_id is just a failsafe really. It looks complicated in MySql, but the concept is common and this is fast. I'd hope OP would include some more filters so it's not the whole table. @Strawberry where else would the `ORDER BY` go though? That order affects the actual ranking sequence in the sub-query, its not meant to sort the overall query. – Chris Schaller Mar 07 '21 at 11:59
  • If I recall correctly, it's the difference between, say, `SELECT *, @i:=@i+1 FROM my_table, (SELECT @i:=1) vars ORDER BY id;` and `SELECT x.* FROM (SELECT *, @i:=@i+1 FROM my_table ORDER BY id)x JOIN (SELECT @i:=1) vars;` In practice, both work fine in <5.7. However, theoretically, the first one is capable of incorrectly assigning @i - I can't find the proper discussion of this in the manual just at the moment, and in any case, these methods are now essentially deprecated. – Strawberry Mar 07 '21 at 12:32