best way to remove gaps in position list

[TechBoyJK]

Hola!

I'm writing a small classified advertising website.

Most of it is done, and I'm just working out the kinks of the photo management component I've written. For the most part it works.

User Functions, Functioning:

-upload up to 20 photos one by one
-upload up to 20 photos in one form (multiples)
-re-arrange photo order (move up, move down)
-delete photos one by one
-delete photos in multiples
-add/edit captions
-set any photo as primary for the classified ad
-bulk edit (select check box of each photo to bulk edit)

Back end Functions, Functioning:

-auto resize of original photo into thumbnail, small, medium, and large versions
-auto delete of original photo after mod versions created
-basic consolidation after photos deleted.. Delete photo with position 17, photo with position 18 gets 17. prevents gaps in positioning


I still need help with two things. I'm not looking for advice on table structure or anything like, just programmatic advice on how to best achieve the results I want.

Here is the table structure:

tbl_classified_photos

classified_photo_id (int-identity-pk)
classified_id (int-fk)
classified_photo_public_url (varchar-255)
classified_photo_private_url (varchar-255)
classified_photo_file (varchar-255)
classified_photo_caption (nvchar-16-null)
classified_photo_file_created (datetime-null)
classified_photo_file_created_ip (varchar-15-null)
classified_photo_file_last_modified (datetime-null)
classified_photo_file_last_modified_ip (varchar-15-null)
classified_photo_position (smallint-2)


First Issue:

Right now, when a photo is deleted, the order is consolidated to make up for the gap of the delete photos. Basically, just after the query runs that deletes the photo, another query runs that sets the position to -1 of whatever photo had a position greater than the position of the photo that was deleted. That works.

What doesn't work is if their is already some kind of gap in positioning. Such as there are photos with positions 1,2,3,4,6,9. I've put measures in place to prevent that from even happening, but if it does, I want a function in place that fixes it. Does anybody know of the best way to get the set in order?

If I run a query and get the following positions

1,2,3,5,9,15

How can I convert that to

1,2,3,4,5,6

 

[EagleKeeper]

When walking through the list;
1) Read an item id from the list
2) For each item id, keep a temp value that is the next one in the order.
3) Read the next item id
4) If the id does not match the expected value, set the value to expect via the temp in #2
5) Treat the correction as if it was from step #2
6) Continue at Step #2

 

[QED]

The following SQL will output a table with the old photo_id and the new photo_id for a particular classified id:

SELECT p1.photo_id, COUNT(*) as new_photo_id FROM
tbl_classified_photos as p1, tbl_classified_photos as p2
WHERE p1.photo_id >= p2.photo_id
AND p1.classified_id = (some classified id)
AND p2.classified_id = (some classified id)
GROUP BY p1.photo_id
ORDER BY new_photo_id

You can then use this table to update the tbl_classified_photos table.

 

[TechBoyJK]

from another forum>>>

------------------------------------------------------------------------


You could ensure that the query for existing order values is in order, then loop over it and build up a structure that maps the primary key to the order, and then create a counter starting at 1 and loop over the structure, updating each record with the new counter-based order value, which should end up giving you values like 1,2,3,4,5... with no gaps.

 

[WannaFly]

I bet in your delete repositioning script you use position+1, use min(position) where position>NUM and that will get you the next lowest number, even if it's not consecutive.

Theres some design flaws here, but what CC said just about sums it up. It sounds like you might benefits from a classified_photo_order table, with one row per ad with 20 columns. But, that limits you to 20 columns.

Also, classified_photo_file - if thats a path to the file, i'd suggest more then 255 char. You never know

 

[TechBoyJK]

Originally posted by: WannaFly
I bet in your delete repositioning script you use position+1, use min(position) where position>NUM and that will get you the next lowest number, even if it's not consecutive.

Theres some design flaws here, but what CC said just about sums it up. It sounds like you might benefits from a classified_photo_order table, with one row per ad with 20 columns. But, that limits you to 20 columns.

Also, classified_photo_file - if thats a path to the file, i'd suggest more then 255 char. You never know

I'm actually going to shorten the length, not extend it.

WHen users upload their files, they are manipulated (resized, thumbnails, etc) and the original is deleted. The original file name never makes it into the table, only a unique string that is generated for the modded files.

 

[JACKDRUID]

I'm not sure what is it that is so important with positioning..? what are you really trying to achieve ?...

 

[TechBoyJK]

Originally posted by: JACKDRUID
I'm not sure what is it that is so important with positioning..? what are you really trying to achieve ?...

the order in which the photos are displayed is dependant on positioning

 

[kamper]

So why do you have to compact the numbers? Just make sure you always insert higher numbers for new pictures and you can still sort with the gaps in there.

 

[TechBoyJK]

Originally posted by: kamper
So why do you have to compact the numbers? Just make sure you always insert higher numbers for new pictures and you can still sort with the gaps in there.

people need to be able to reposition them.

 

[QED]

Originally posted by: QED
The following SQL will output a table with the old photo_id and the new photo_id for a particular classified id:

SELECT p1.photo_id, COUNT(*) as new_photo_id FROM
tbl_classified_photos as p1, tbl_classified_photos as p2
WHERE p1.photo_id >= p2.photo_id
AND p1.classified_id = (some classified id)
AND p2.classified_id = (some classified id)
GROUP BY p1.photo_id
ORDER BY new_photo_id

You can then use this table to update the tbl_classified_photos table.

Did this not work for you? After running the above SQL query, you can then simply assign photo_id to new_photo_id (which will be in order-- 1, 2, 3, 4, 5, 6, etc. with no gaps).

If your database supports UPDATE statements with dynamic tables, then you can actually accomplish all of this
with one query:

UPDATE tbl_classified_photos
SET photo_id = cp.new_photo_id
FROM
( SELECT p1.photo_id, COUNT(*) as new_photo_id FROM
tbl_classified_photos as p1, tbl_classified_photos as p2
WHERE p1.photo_id >= p2.photo_id
AND p1.classified_id = (some classified id)
AND p2.classified_id = (some classified id)
GROUP BY p1.photo_id) AS cp
WHERE tbl_classified_photos.photo_id = cp.photo_id.

 

[JACKDRUID]

Originally posted by: TechBoyJK

Originally posted by: kamper
So why do you have to compact the numbers? Just make sure you always insert higher numbers for new pictures and you can still sort with the gaps in there.

people need to be able to reposition them.

then you should have another column on your database named "order" contain order of pictures.

ID should never ever be used for anything other than IDing a record.

 

[TechBoyJK]

Originally posted by: JACKDRUID

Originally posted by: TechBoyJK

Originally posted by: kamper
So why do you have to compact the numbers? Just make sure you always insert higher numbers for new pictures and you can still sort with the gaps in there.

people need to be able to reposition them.

then you should have another column on your database named "order" contain order of pictures.

ID should never ever be used for anything other than IDing a record.

I have that, if you look at the table structure at the top, the column is titled "position"

It's a column seperate from the main ID.

 

[EagleKeeper]

So just do a requery of the table (using what ever parameters), sorted by ID
Read each record and reassign the index to be in the new sequence.

 

[JACKDRUID]

Originally posted by: TechBoyJK

I have that, if you look at the table structure at the top, the column is titled "position"

It's a column seperate from the main ID.

k now I see what you are trying to do

well, i would suggest 2 more columns on your table...
very much like a doubly linked list
1. "prev", "next" , contain "position" of previous and next picture's "position"
2. when a picture is deleted, its previous picture's "next" will be set to the deleted picture's "next", and the next picture's "previous" will be set to deleted picture's "previous"
3. the next picture's "position" will be set to deleted picture's "position"

repeat until you get to the last record..


above is very slow and ineffecient btw..

why can't you just order them? do you really need the static "position"?