0

i have an architecture issue to solve with a rails app. I have gallery of images people can vote (up or down) for each day. Theses images get sorted by their score and their created_at timestamp so we got a top of day image galleries with 48 images (even if there were more than 48 images uploaded during the day).

Along with the top of the day you can see the top images of yesterday and two days before … I want to add the feature that allows someone to see the previous image of the gallery whenever he pick one image … in other words you can pick images 5/48 and be able by clicking on an arrow to go to the 4/48 images (the previous one) …

In the same way the previous image of the the first top image of yesterday should lead you to the last top image of today …

It may be simple but i can't find the right query to do the job … any suggestions please ?

Thanks

user318722
  • 215
  • 3
  • 11
  • Can you share your DB schema? Do you have a separate top images table? What columns does it have? Do you keep a sum of up/down votes in the images table? I guess you want something like ```ORDER BY created_at DESC, score DESC``` – Teoulas Jun 26 '12 at 16:24
  • No i don't have a separate top images table (even if i think it can be a good idea to do some OLAP with a separate top images table). The schema is pretty basic : i keep a sum of up/down votes in a score column and so far yes i have used ORDER BY created_at DESC, score DESC – user318722 Jun 26 '12 at 18:32
  • I made a mistake, it's `ORDER BY created_at ASC, score DESC` since you want the previous link to go back in time to the previous day, after reaching the top scoring image. – Teoulas Jun 26 '12 at 21:43

2 Answers2

0

You are talking about breadcrumbs. I have not worked with Ruby, but here's a different question who's answer may be what you're looking for

Breadcrumbs in Ruby on Rails

Community
  • 1
  • 1
ZnArK
  • 1,533
  • 1
  • 12
  • 23
  • no i am not talking about breadcrumbs, breadcrumb is keeping an history of links visited that's not what i want. Imagine you open a page and look directly at the fourth image on this page, and you would like to go to the third one just after (previous image) – user318722 Jun 26 '12 at 18:35
0

Well, if you already have your query that gets you the images in the correct order and know the current image, it's easy to find the next/previous one. Assuming @id is your current image id, you could do something like this:

top_images = Image.order("created_at ASC, score DESC")
prev = top_images.where("id < ?", @id).limit(1)
next = top_images.where("id > ?", @id).limit(1)

It makes more sense to ORDER BY created_at ASC, score DESC if you want a "previous" link to go back in time after reaching the top ranked image for this day.

Teoulas
  • 2,943
  • 22
  • 27