Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2016
    Posts
    3

    Question Answered: How to re-position elements like images in a gallery?

    Let's say I have a user gallery in which the user can freely drag around images to change their position, every time they do so an ajax call is sent to the server to save the new positions. (Here's an animation: https://i.sli.mg/AXQhtc.gif )

    What would be the best approach to update the positions in postgres?

    (In Mysql I would had used a variable and consecutively number the images within a gallery every time a user drags an image but I don't know how to do that in Postgres and I'm wondering if there might be a better solution than to update dozens of rows consecutively).

    Thank you!
    Attached Thumbnails Attached Thumbnails AXQhtc.gif  

  2. Best Answer
    Posted by shammat

    "If I understand you correctly, you want to move the image to a new position and then push all items "after" that new position down by one.

    The new number for the position can be generated using something like this:

    Code:
    with image_info as (
       select imageid, 
              galleryid,
              "position" as old_pos, 
              1 as new_pos -- this is the new position (taken from the request)
       from galleryimages
       where imageid = 4    -- this is the image to be moved (taken from the request)
         and galleryid = 1  -- this is the gallery from the request
    ), renumbered as (
      select gi.imageid, title, 
             gi."position" as old_position,
             case 
                when gi.imageid = inf.imageid then inf.new_pos
                when "position" >= inf.old_pos and "position" <= inf.new_pos then "position" - 1
             end as calculated_new_position
      from galleryimages gi
        join image_info inf on gi.galleryid = inf.galleryid
    )
    select imageid, 
           title, 
           old_position,
           row_number() over (order by coalesce(calculated_new_position, old_position)) as new_position
    from renumbered
    order by 4;
    For your example this outputs the following:

    Code:
    imageid | title            | old_position | new_position
    --------+------------------+--------------+-------------
          1 | A family photo   |            1 |            1
          4 | this is me       |            4 |            2
          2 | Another Selfie   |            2 |            3
          3 | Me in the garden |            3 |            4
          5 | me and my son    |            5 |            5
    The first CTE is essentially only there to avoid repeating the those three values in the second CTE. I also used two statements to make it clearer on how the new position is calculated. The second CTE and the final select can be combined into a single one and this can then be used to do the actual update:

    Code:
    with image_info as (
       select imageid, 
              galleryid,
              "position" as old_pos, 
              1 as new_pos -- this parameter needs to be passed to the statement (taken from the request)
       from galleryimages
       where imageid = 4    -- this is the image to be moved (taken from the request)
         and galleryid = 1  -- this is the gallery from the request
    ), renumbered as (
      select gi.imageid, 
             gi."position" as old_position,
             row_number() over (order by coalesce(case 
                                                    when gi.imageid = inf.imageid then inf.new_pos
                                                    when "position" >= inf.old_pos and "position" <= inf.new_pos then "position" - 1
                                                  end, gi."position")) as new_position
      from galleryimages gi
        join image_info inf on gi.galleryid = inf.galleryid
    )
    update galleryimages
      set "position" = r.new_position
    from renumbered r
    where r.imageid = galleryimages.imageid
      and r.new_position <> galleryimages."position" -- only change rows that actually change
    Note that position is a keyword and thus needs to be quoted to avoid any ambiguity.

    Here is a SQLFiddle demo of the above: http://sqlfiddle.com/#!15/a669b/1"


  3. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please show us the table definition (as a create table statement), some sample data, the user input you have and the expected outcome from running the statement.

    Make sure you properly format everything to make it readable.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #3
    Join Date
    Jan 2016
    Posts
    3
    Tables:

    Code:
    DROP TABLE IF EXISTS Galleries;
    
    CREATE TABLE Galleries (
        galleryid   SERIAL PRIMARY KEY,
        userid      INTEGER,
        title       TEXT,
        created     TIMESTAMP
    );
    
    DROP TABLE IF EXISTS GalleryImages;
    
    CREATE TABLE GalleryImages(
        imageid     SERIAL PRIMARY KEY,
        galleryid   INTEGER,
        title       TEXT,
        uploaded    TIMESTAMP,
        position    INTEGER
    );
    Sample Data:

    Code:
    INSERT INTO Galleries (
        userid, title, created
    ) VALUES
    (1, 'This is me, Jennifer', '1990-01-03 19:00:00'),
    (1, 'This is my Cat',       '1990-01-03 20:30:00');
    
    
    
    
    INSERT INTO GalleryImages(
        galleryid, title, uploaded, position
    ) VALUES
    (1, 'A family photo',       '1990-01-03 19:01:00', 1),
    (1, 'Another Selfie',       '1990-01-03 19:05:00', 2),
    (1, 'Me in the garden',     '1990-01-03 19:10:00', 3),
    (1, 'this is me',           '1990-01-03 19:12:00', 4),
    (1, 'me and my son',        '1990-01-03 19:15:00', 5),
    
    (2, 'My Cat Felix',         '1990-01-03 20:35:00', 1),
    (2, 'So cute',              '1990-01-03 20:40:00', 2),
    (2, 'purr purr',            '1990-01-03 20:42:00', 3),
    (2, 'Felix eating lasagne', '1990-01-03 20:44:00', 4),
    (2, 'rawr',                 '1990-01-03 20:48:00', 5);
    User calls:

    localhost/moveimage.php?galleryid=1&imageid=4&toposition=1
    localhost/moveimage.php?galleryid=2&imageid=7&toposition=4


    Desired Result:

    Code:
    SELECT * FROM GalleryImages
    
    imageid     title                uploaded               position
    
    1           A family photo       1990-01-03 19:01:00    2
    2           Another Selfie       1990-01-03 19:05:00    3
    3           Me in the garden     1990-01-03 19:10:00    4
    4           this is me           1990-01-03 19:12:00    1
    5           me and my son        1990-01-03 19:15:00    5
    
        
    6           My Cat Felix         1990-01-03 20:35:00    1
    7           So cute              1990-01-03 20:40:00    4
    8           purr purr            1990-01-03 20:42:00    2
    9           Felix eating lasagne 1990-01-03 20:44:00    3
    10          rawr                 1990-01-03 20:48:00    5

  5. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If I understand you correctly, you want to move the image to a new position and then push all items "after" that new position down by one.

    The new number for the position can be generated using something like this:

    Code:
    with image_info as (
       select imageid, 
              galleryid,
              "position" as old_pos, 
              1 as new_pos -- this is the new position (taken from the request)
       from galleryimages
       where imageid = 4    -- this is the image to be moved (taken from the request)
         and galleryid = 1  -- this is the gallery from the request
    ), renumbered as (
      select gi.imageid, title, 
             gi."position" as old_position,
             case 
                when gi.imageid = inf.imageid then inf.new_pos
                when "position" >= inf.old_pos and "position" <= inf.new_pos then "position" - 1
             end as calculated_new_position
      from galleryimages gi
        join image_info inf on gi.galleryid = inf.galleryid
    )
    select imageid, 
           title, 
           old_position,
           row_number() over (order by coalesce(calculated_new_position, old_position)) as new_position
    from renumbered
    order by 4;
    For your example this outputs the following:

    Code:
    imageid | title            | old_position | new_position
    --------+------------------+--------------+-------------
          1 | A family photo   |            1 |            1
          4 | this is me       |            4 |            2
          2 | Another Selfie   |            2 |            3
          3 | Me in the garden |            3 |            4
          5 | me and my son    |            5 |            5
    The first CTE is essentially only there to avoid repeating the those three values in the second CTE. I also used two statements to make it clearer on how the new position is calculated. The second CTE and the final select can be combined into a single one and this can then be used to do the actual update:

    Code:
    with image_info as (
       select imageid, 
              galleryid,
              "position" as old_pos, 
              1 as new_pos -- this parameter needs to be passed to the statement (taken from the request)
       from galleryimages
       where imageid = 4    -- this is the image to be moved (taken from the request)
         and galleryid = 1  -- this is the gallery from the request
    ), renumbered as (
      select gi.imageid, 
             gi."position" as old_position,
             row_number() over (order by coalesce(case 
                                                    when gi.imageid = inf.imageid then inf.new_pos
                                                    when "position" >= inf.old_pos and "position" <= inf.new_pos then "position" - 1
                                                  end, gi."position")) as new_position
      from galleryimages gi
        join image_info inf on gi.galleryid = inf.galleryid
    )
    update galleryimages
      set "position" = r.new_position
    from renumbered r
    where r.imageid = galleryimages.imageid
      and r.new_position <> galleryimages."position" -- only change rows that actually change
    Note that position is a keyword and thus needs to be quoted to avoid any ambiguity.

    Here is a SQLFiddle demo of the above: http://sqlfiddle.com/#!15/a669b/1
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #5
    Join Date
    Jan 2016
    Posts
    3

    Thumbs up

    Thank you!

    After analyzing your approach I came up with a somewhat simpler one by splitting it into two queries. First I renumber the entries and then move the image.


    Code:
    UPDATE GalleryImages
    SET position = 
      CASE WHEN wims.newrownumber < 5 THEN wims.newrownumber 
      ELSE 1+wims.newrownumber END
    FROM(
      SELECT imageid, 
         row_number() OVER (order by "position") AS newrownumber
      FROM 
        GalleryImages WHERE galleryid = 2 AND imageid <> 6
    ) wims
    WHERE GalleryImages.imageid = wims.imageid AND (GalleryImages ."position" <> wims.newrownumber OR GalleryImages ."position" = 5);
    
    UPDATE GalleryImages SET "position" = 5 WHERE imageid = 6;


    Anyway you helped me a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •