Tech Off Thread

9 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Grouping and sorting images in SQL

Back to Forum: Tech Off
  • User profile image
    qwert231

    I have 2 tables (in this example). Groups and Photos. Groups looks something like this:
    GroupID GroupName InfoAboutGroup GroupSort
    1    Formal           'This is a group of formal pictures.'            3
    2    Ceremony      'These are pictures from the Ceremony.'  1
    3    Reception      'These are pictures from the Reception.'   2
    4    UnGrouped    'These pictures are not grouped.'             4

    Photos looks like this:
    PhotoID ImageName GroupID GroupSort
    321456    DCF0001.jpg 2  1
    321457    DCF0001.jpg 2  3
    321458    DCF0001.jpg 2  2
    321459    DCF0001.jpg 3  1
    3214510  DCF0001.jpg 1  1

    When I pull the photos for a job they are sorted like this:
    Groups.GroupSort, Photos.GroupSort

    Groups.GroupSort is how the groups should be sorted. Photos.GroupSort is how the photos are sorted in their group. No problem with the Select portion of this.

    What I'm having trouble with is organizing them to begin with. The groups are set up previously, but the Photos come in with GroupID 4 and Null sort.

    What is the best way to design my Stored Procedures so that I can easily group and sort these images. I've been trying a stored procedure like this:
    CREATE PROCEDURE PhotoGroupUpdate
    @PhotoID int,
    @GroupID int
     AS
    DECLARE
     @HighSort int,
     @OrderNum int

    SELECT @OrderNum = ImageOrderNum FROM Photos WHERE PhotoID = @PhotoID
    SELECT @HighSort = MAX(GroupSort) + 1 FROM Photos WHERE ImageOrderNum = @OrderNum AND GroupID= @GroupID
    If @HighSort IS Null
    BEGIN
    SELECT @HighSort = 1
    END
    UPDATE Photos SET GroupID = @GroupID, GroupSort = @HighSort WHERE PhotoID = @PhotoID
    GO

    So that every time an image is added to a group, it get's the next sort number. But if we move an image out of that group, we're left with a hole.

    I'm just not sure if I'm heading in the right direction. Thanks.

  • User profile image
    csharp1171

    Is Photos.PhotoID an autonumber?  If yes why not sort off that column?

  • User profile image
    Maurits

    qwert231 wrote:
    But if we move an image out of that group, we're left with a hole.


    Is that a problem?

  • User profile image
    qwert231

    Photos.PhotoID is an Autonumber, and that is precisely the reason we cannot sort by it. Just because 269302 comes before 269303 does not mean that we want 269302 to appear first.

    As far as the holes, Let's say we've got images in a group sorted like this:
    PhotoID ... GroupID GroupSort
    269303      2            1
    269305      2            2
    269309      2            3
    269302      2            4
    269310      2            5

    And we want to move 269302 between 269303 and 269305. (I guess you could say we want to insert it.) We can't just change 369302's GroupSort to 2, because that wouldn't guarantee that it would appear before 269305.

    That's the kind of thing I'm dealing with.

  • User profile image
    csharp1171

    Just a thought.  When a photo is inserted default the sort order to the PhotoID.  If you need to change the sort order with another record then update both records with the other sort order.  For example:

    PhotoID ... GroupID GroupSort
    269303      2            269303
    269305      2            269305
    269309      2            269309
    269302      2            269302
    269310      2            269310

    To change the sort order of 269309 above 269305:

    PhotoID ... GroupID GroupSort
    269303      2            269303
    269305      2            269309
    269309      2            269305
    269302      2            269302
    269310      2            269310

  • User profile image
    qwert231

    That would work for photos we want to swap. But what we want is more like this:
    PhotoID ... GroupID GroupSort
    269303      2            1
    269305      2            2
    269309      2            3
    269302      2            4
    269310      2            5

    PhotoID ... GroupID GroupSort
    269303      2            1
    269310      2            2
    269305      2            3
    269309      2            4
    269302      2            5


    And then maybe they'd do this:
    269310      2            1
    269305      2            2
    269303      2            3 ' This one moved in sequence
    269309      2            4
    269302      3            1 ' This one moved to another group.

  • User profile image
    Maurits

    You can get rid of gaps by running this SQL:

    DECLARE @dummy bit

    -- first do a no-op SELECT to set @@ROWCOUNT
    SELECT
        @dummy = 1

    -- keep running the UPDATE until no more rows are affected
    WHILE (@@ROWCOUNT > 0)
    BEGIN

    UPDATE
        Photos
    SET
        GroupSort = GroupSort - 1
    WHERE
        GroupSort > 1 AND
        -- there is a hole immediately below...
        (GroupSort - 1) NOT IN
        (
           SELECT
              P.GroupSort
           FROM
              Photos AS P
           WHERE
              P.PhotoID = Photos.PhotoID AND
              P.GroupID = Photos.GroupID
        )

    END

    There may be a cleverer way to do it, too...

  • User profile image
    Maurits

    If you want to move a photo from one group to another:
    1) Change the GroupID to the new group, and simultaneously set the GroupSort to one higher than the number of photos in the new group
    2) Run the "eliminate gaps" SQL to close the gap in the old group.  If the photo was the last one in the old group, this is a no-op.

    If you want to move a photo from one place in a group to another place in the same group...

    -- @PhotoID, @GroupID are parameters
    -- @NewGroupSort holds the new location and is also a parameter

    DECLARE @OldGroupSort int -- (or whatever data type)

    SELECT
        @OldGroupSort = GroupSort
    FROM
        Photos
    WHERE
        PhotoID = @PhotoID AND
        GroupID = @GroupID

    IF @OldGroupSort > @NewGroupSort
    BEGIN

        -- moving closer to 1
        UPDATE
           Photos
        SET
           GroupSort =
              CASE
                 -- this one moves down
                 WHEN GroupSort = @OldGroupSort
                 THEN @NewGroupSort

                 -- everything in between goes up one
                 ELSE GroupSort + 1

              END
        WHERE
           GroupID = @GroupID AND
           GroupSort BETWEEN @NewGroupSort AND @OldGroupSort

    END
    ELSE IF @NewGroupSort > @OldGroupSort
    BEGIN

        -- moving away from one
        UPDATE
           Photos
        SET
           GroupSort =
              CASE
                 -- this one moves up
                 WHEN GroupSort = @OldGroupSort
                 THEN @NewGroupSort

                 -- everything in between goes down one
                 ELSE GroupSort - 1

              END
        WHERE
           GroupID = @GroupID AND
           GroupSort BETWEEN @OldGroupSort AND @NewGroupSort

    END

    -- No gaps are created in this case

  • User profile image
    Maurits

    Maurits wrote:

    UPDATE
        Photos
    SET
        GroupSort = GroupSort - 1
    WHERE
        GroupSort > 1 AND
        -- there is a hole immediately below...
        (GroupSort - 1) NOT IN
        (
           SELECT
              P.GroupSort
           FROM
              Photos AS P
           WHERE
              P.PhotoID = Photos.PhotoID AND
              P.GroupID = Photos.GroupID
        )

    There may be a cleverer way to do it, too...


    Got it... don't need the loop with this version, or the dummy select...

    UPDATE
        Photos
    SET
        -- the GroupSort field should be equal to the number
        -- of photos in that group with a lower GroupSort, plus one
        GroupSort =
        (
           SELECT
              COUNT(*)
           FROM
              Photos AS P
           WHERE
              P.PhotoID = Photos.PhotoID AND
              P.GroupID = Photos.GroupID AND
              P.GroupSort < Photos.GroupSort
        ) + 1
    WHERE
        GroupSort >
        (
           SELECT
              COUNT(*)
           FROM
              Photos AS P
           WHERE
              P.PhotoID = Photos.PhotoID AND
              P.GroupID = Photos.GroupID AND
              P.GroupSort < Photos.GroupSort
        ) + 1

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.