Rails - Complicated SQL Query

Question

I need help with a query that does the following:

Start from the newest record and go downwards to the older records.
It needs to be ordered by created_at time.
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.
I want to only get only 16 records at a time. That number can change later.
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.

Is it even possible to to my requirements in one sql query? Or do I have to do it in two queries? — Scott Deutsch
– Scott Deutsch, Commented Jan 13, 2013 at 11:04

Hauleth · Accepted Answer · 2013-01-13 11:07:11Z

3

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.

edited Jan 13, 2013 at 11:07

answered Jan 13, 2013 at 1:13

Hauleth

23.7k4 gold badges67 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Scott Deutsch Over a year ago

When I do it that way, I get the following error: undefined method `in' for #<ActiveRecord::Relation:0x007f8a3d5740a8>

PinnyM Over a year ago

I believe these should be defined as class methods (i.e. def self.in(args) and def self.from(time))

Scott Deutsch Over a year ago

Ok, I added the self.in and self.from in the functions. Now I am getting this error: SQLite3::SQLException: near "0": syntax error: SELECT "rss_entries".* FROM 0 WHERE "rss_entries"."id" IN (3, 4) ORDER BY rss_entries.created_at DESC LIMIT 16 OFFSET 0

Scott Deutsch Over a year ago

That error looks like it is not selecting the database, but 0? Why is it doing that from the code above.

Scott Deutsch Over a year ago

Ok, so I changed the function self.from to self.timeCheck and now it is selecting the database correctly, But there is another problem. It is not working as expected. It gives me reults the first run, but after the argument lid is not 0, it does not work.

Collectives™ on Stack Overflow

Rails - Complicated SQL Query

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related