CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-3
Scott Deutsch
Scott Deutsch

Reputation: 657

Rails - Complicated SQL Query

I need help with a query that does the following:

  1. Start from the newest record and go downwards to the older records.
  2. It needs to be ordered by created_at time.
  3. If there are new records in the database by created_at time, retrive them but do not get records I already got from step 1.
  4. I want to only get only 16 records at a time. That number can change later.
  5. Do not retrive records I already sent from a previous time.

Also just to let you know, this is started via $.ajax.

Reason for this is because I am getting new + old records real-time to be sent to the client. Think something like like user starts off visiting the website and it gets the current records starting with new ones. Then the user can go get older records, but at the same request, it also retrieves the brand new records. With a twist of only 16 records at a time.

Do I make sense?

This is what I currently have for code:

RssEntry.includes(:RssItem).where("rss_items.rss_id in (?) AND (rss_entries.id < ? OR rss_entries.created_at > ?)", rssids, lid, time).order("rss_entries.id DESC").limit(16)

lid = last lowest id from those records
rssids = ids from where to get the records
time = last time it did the records call

That code above is only the beginning. I now need help to make sure it fits my requirements above.

UPDATE 1

Ok, so I managed to do what I wanted but in 2 sql queries. I really don't think it is possible to do what I want in one sql query.

Any help is greatly appreciated.

Thanks.

Upvotes: 1

Views: 159

Answers (1)

Hauleth
Hauleth

Reputation: 23556

Firstly, use scopes to get what you want:

class RssEntry < ActiveRecord::Base
  scope :with_items, includes(:RssItem)
  scope :newer_first, order("rss_entries.created_at DESC")

  def self.in(args)
    where(id: args)
  end

  def self.since(time)
    where('rss_entries.created_at > ?', time)
  end
end

then

RssEntry.with_items.in(rssids).since(time).offset(lid).limit(16).newer_first

It should work as expected.

Upvotes: 3

Related Questions