little complex sql row postion

Question

basically i have albums, which has 50 images init.. now if i show list of images, i know from which to which row is showing (showing: 20 to 30 of 50), means showing 10 rows from 20 - 30. well now the problem is, i want to select an image, but still show which postion was it selected, so i can move back and forth, but keep the postion too.

like if i select 5th image, which id is 'sd564', i want to show (6 of 50 images), means you are seeing 6th of 50 images.. if i get next row id and show that, then, i want to show (7 of 50 images).

well i can do all this from pagination pointer easily, like in url say (after=5, after=6)... its moving with postion, but what if i dont have this (after=6) and just have an id, how can i still do that?

i dont want to use (after=6) also because its dynamic site and images adds and delete, so position chnages and sharing with someone else and going back on same old link, then it would be wrong position.

what kind of sql query should i be running for this?

currently i have

select * from images where id = 'sd564'; 

obviously i need to add limit or some other thing in query to get what i want or maybe run another query to get the result, while keeping this old query inplace too. anyway i just want positioning. i hope you can help me solve this

Example: http://media.photobucket.com/image/color%20splash/aly3265/converse.jpg

Album Query Request (check post below)

select images.* from images, album
where album_id = '5'
and album_id = image_album_id
order by created_date DESC
limit ....;

Answer 1

From what you are saying here:

dont want to use (after=6) also because its dynamic site and images adds and delete, so position chnages and sharing with someone else and going back on same old link, then it would be wrong position.

I get the impression that this is not a SQL problem at all. The problem is that the positions of the fotos are local to the search resultset. To reliably naviate by position, you would need to make a snapshot (no pun intended) of some kind. That is, you need to have some way to "freeze" the dataset while it is being browsed.

A simple way to do it, would be to execute the search, and cache the result outside of the actual current datastore. For example, you could use "scratch tables" in your database, simply store it in temporary files, or in some memory caching layer if you have the mem for it. With this model, you'd let the user browse the resultset from the cache, and you would need to clean out the cache when the user's session ends (or after some timeout, you don't want to kill your server because some users don't log out)

Another way to do it, is to simply allow yourself to lie now and then. Let's say you have result pages of 10 images, and a typical search delivers 50 pages of results. Well, you could simply send a resultset for a fixed number of items, say 100 photos (so 10 pages) to the client. These search results would then be your snapshot, and contain references to the actual pictures. If you are storing the URLS in the database , and not the binary data, this reference is simply the URL. Or you could store the database Id there. Anyway, the user is allowed to browse the initial resultset, and chances are that they never browse the entire set. If they do, you re-execute the query on the server side for the next chunk of pages. If many photos were added in the mean time that would end up at positions 1..100, then the user will see stale data: that's the price they pay for having so much time on their hands that they can allow themselvs to browse 10 pages of 10 photos.

(of course, you should tweak the parameters to your liking but you get the idea I'm sure.)

If you don't want to 'lie' and it is really important that people can reliably browse all the results they searched, you could extend your database schema to support snapshots at that level. Now asssuming that there are only two operations for photos, namely "add" and "delete", you would have a TIMESTAMP_ADDED and a TIMESTAMP_REMOVED in your photo table. On add, you do the INSERT in your db, and fill TIMESTAMP_ADDED with the currrent timestamp. The TIMESTAMP_REMOVED would be filled with the theoretical maximum value for whatever data type you like to use to store the timestamp (For this particular case I would probably go for an INT column and simply store the UNIX_TIMESTAMP) On delete, you don't DELETE the row from the db, rather, you mark it as deleted by updating TIMESTAMP_REMOVED column, setting it to the current timestamp. Now when you have to do a search, you use a query like:

SELECT    *
FROM      photo
WHERE     timestamp_added   < timestamp_of_initial_search
AND       timestamp_removed > timestamp_of_initial_search
AND      ...various search criteria...
ORDER BY ...something
LIMIT    ...page offset and num items in page...

The timestamp_of_initial_search is the timestamp of executing the initial search for a particular set of criteria. You should store that in the application session while the user is browsing a particular search resultet so you can use that in the subsequent queries required for fetching the pages. The first two WHERE criteria are there to implement the snapshot. The condition timestamp_added < timestamp_of_initial_search ensures we can only see photos that were added before the timestamp of executing the search. The condition timestamp_removed > timestamp_of_initial_search ensures we only search that were not already removed by the time the initial search was executed.

Of course, you still have to do something with the photos that were marked for delete. You could schedule periodical physical deletion for all photos that have a timestamp removed that is smaller than any of the current search resultsets.

Answer 2

If I understood your problem correctly, you can use the Row_Number() function (in SQL Server). To get the desired result, you can use a query something similar to this:

select images1.* from 
    (SELECT ROW_NUMBER() OVER (ORDER BY image_album_id) as rowID,(SELECT COUNT(*) FROM images) AS totCount, * FROM images) images1
JOIN album ON (album_id = images1.image_album_id)
where album_id = '5' 
order by images1.image_album_id 
limit ....;

Here the images.rowid gives you the position of the row and images.totCount give you the total number of rows.

Hope it helps.

Thnks.

Answer 3

Assuming created_date is unique per album_id and (album_id,created_date) is unique for all rows in images, then this:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.album_id = 5
group by   i1.created_date

will reliably get you the images and their position. Please understand that this will only work reliably in case (album_id,created_date) are unique throughout the images table. If that is not the case, the position wont be reliable, and you might not see all photos due to the GROUP BY. Also note that a GROUP BY clause like this, only listing some of the columns that appear in the SELECT list (in this case images.*) is not valid in most RDBMS-es. For a detailed discussion on that matter, see: http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

By doing this:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.album_id = 5
group by   i1.created_date
having     count(*) = 4

you select the image at the 4th position (note the having count(*) = 4)

By doing this:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.album_id = 5
group by   i1.created_date
having     count(*) between 1 and 10

you select all photos with positions 1 through 10 (note the having clause again.)

Of course, if you just want one particular image, you can simply do:

select     i1.*, count(*) as position
from       images i1
inner join images i2
on         i1.album_id      = i2.album_id     -- get all other pics in this album
and        i1.created_date >= i2.created_date -- in case they were created before this pic
where      i1.image_id = 's1234' 
group by   i1.created_date

This will correctly report the position of the image within the album (of course, assuming that image_id is unique with in the images table). You don't need the having clause in that case since you already pinpointed the image you want.