get previous/next item in database

[Alex]

Hi,

I’m trying to get the previous and next item (articles) from a database with Datamapper. I thought of doing it with the serial identifier 

property :id,         Serial

and increment/decrease this number to find the previous/next posts of that particular article, but it doesn’t work. Because the serial is incremented fine for new articles (1,2,3,4,5), but if you delete say id: #3, then the next article of #2 will be #4, not #3.

Do you know if there is an easier/more correct way to do it with Datamapper?

[Ben Lovell]

Hey Alex,

Sounds like you're missing a concept in your application. Perhaps you should add some kind of ordering/page column to your table if you need to be explicit about ordering? This way you could rearrange the ordering if necessary, ensure uniqueness for pages/ordering and other goodness.

Alternatively, if you're storing created_at timestamps on your tables you could use this as a surrogate for ordering.

Cheers,

Ben

[kristian]

what about doing something like this :

current_id = Audit.first.id
next = Audit.first( :id.gt => current_id )

- Kristian

[Alex]

Thanks Ben. I do store timestamps in the table. Would you suggest I should check for older/newer timestamps inside this loop?

  #defines if a post of a user exists in db
  def post_in_db
    posts_of_user.each do |post|
      if post.slug == params[:post_slug]
        @post_slug ||= post.slug
        @post_title ||= post.title
        @post_body ||= post.body
        @post_date ||= post.created_on
      end
    end
    if @post_body == nil 
      redirect ('/' + params[:nickname])
    end
  end

or I should store the @post_date and create a new helper with a new loop to find the immediate older/newer timestamp before/after the @post_date? I’m thinking in terms of performance the first method would be better.

[Alex]

@Kristian Thanks, I haven’t tried it yet, but how does that get the next id? With the :id.gt ? How about the previous id?

[Ben Lovell]

I'd just do a check for created_on < current for previous and created_on > current for next and just call `first` with the predicate.

For example:

class Post

  def prev

    first(:created_at.lt => self.created_at)

  end

  def next

    first(:created_at.gt => self.created_at)

  end

  #... 

end

Note: This is gmail code and unchecked/tested :)

Cheers,

Ben

[kristian]

gt == great-than

so the previous id you get with

previous = Audit.first( :id.lt => current_id )

lt == less-than or lower-than or so

and id are unique per definition and they get automatically created by
sequence or similar

FYI next and previous can be nil !

- Kristian

> --
> You received this message because you are subscribed to the Google Groups
> "DataMapper" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/datamapper/-/bEe_NnzF3MAJ.
>
> To post to this group, send email to datam...@googlegroups.com.
> To unsubscribe from this group, send email to
> datamapper+...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/datamapper?hl=en.

[Alex]

Thanks guys, the code works, however there is a problem. The .gt works fine, but the .lt always gives me the first element in the db, not the previous element! I tried it with both the :id (@post_id) and the :created_on (@post_date) properties, getting the same result:

  def post_in_db
    posts_of_user.each do |post|
      if post.slug == params[:post_slug]
        @post_slug ||= post.slug
        @post_title ||= post.title
        @post_body ||= post.body
        @post_date ||= post.created_on

        @post_private ||= post.private
        @post_id ||= post.id
        @previous = prev.title
        @next = nexts.title
      end
    end
  end
  def prev
    Post.first(:created_on.lt => @post_date)
  end
  def nexts
    Post.first(:created_on.gt => @post_date)
  end

Any ideas?

[Ben Lovell]

Add some ordering to the posts, this should return the correct records:

On 22 January 2013 17:13, Alex <al...@sicanstudios.com> wrote:

Thanks guys, the code works, however there is a problem. The .gt works fine, but the .lt always gives me the first element in the db, not the previous element! I tried it with both the :id (@post_id) and the :created_on (@post_date) properties, getting the same result:

  def post_in_db
    posts_of_user.each do |post|
      if post.slug == params[:post_slug]
        @post_slug ||= post.slug
        @post_title ||= post.title
        @post_body ||= post.body
        @post_date ||= post.created_on

        @post_private ||= post.private
        @post_id ||= post.id
        @previous = prev.title
        @next = nexts.title
      end
    end
  end
  def prev

    Post.first(:created_on.lt => @post_date, :order => [:post_date.asc])
  end
  def nexts
    Post.first(:created_on.gt => @post_date, :order => [:post_date.asc])
  end

Any ideas?

Again, I have to add this is untested gmail code ;)

Cheers,

Ben 

[Alex]

Thanks a lot, Ben! Got it working by ordering the one in descending and the other in ascending order:

  def prev
    Post.first(:created_on.lt => @post_date, :order => [:created_on.desc])
  end
  def nexts
    Post.first(:created_on.gt => @post_date, :order => [:created_on.asc])
  end

Thanks guys,

Alex 

[kristian]

take last instead of first :)

> Audit.last( :id.lt => 10 )
=> #<Audit @id=9

- Kristian

> --
> You received this message because you are subscribed to the Google Groups
> "DataMapper" group.
> To view this discussion on the web visit

> https://groups.google.com/d/msg/datamapper/-/278-qTqCbrsJ.

[kristian]

be aware that timestamps are not unique, i.e. they usually have no
millis or nanos. just a thought whether using them to iterate over
them.

- Kristian

[Ben Lovell]

On 22 January 2013 17:51, kristian <m.kri...@web.de> wrote:

be aware that timestamps are not unique, i.e. they usually have no
millis or nanos. just a thought whether using them to iterate over
them.

- Kristian

On Tue, Jan 22, 2013 at 11:16 PM, kristian <m.kri...@web.de> wrote:
> take last instead of first :)
>
>> Audit.last( :id.lt => 10 )
> => #<Audit @id=9
>
> - Kristian
>

Heh, first time I've ever participated in pair-programming via email :)

Cheers,

Ben

[kristian]

is a pleasure :))

[Alex]

Yeah, it works with the .last. I might switch to the :id.

Thanks Kristian and Ben! You rock!

Alex