Rails ActiveRecord/SQL Query best practice

Question

I've got a table which has the following columns:

show_days_before_start (integer)
start_time (DATETEXT)

The purpose of the "show_days_before_start" column is to add the days onto the "start_time" to determine when the record should start appearing in results (if it's outside of this time then it should not be shown) (this column could have value from 7 to 21), and the "start_time" is just a datetime stamp of when the event the record is referring to starts.

I need a query to do the above and to return the relevant records. What is the best way to approach this in terms of query design?

davidb · Accepted Answer · 2012-01-13 14:28:51Z

2

This is the wrong way to do it. You should generate the show date when inserting the dataset to the database! Add a column called display_time:

add_column :table_name, :display_time, :timestamp

Then in the model you add attr_reader and attr_accessor for display_time_reader

attr_accessor :display_time_reader
attr_reader :display_time_reader

Then you define the display_time_reader= method:

def display_time_reader=(days_before_start)
     self.display_time=self.start_time-days_before_start.days
end

Then you add a text_field :display_time_reader (You can also take a dropdown with values 7..21) to your _form.html.erb and when you enter 5 it will save the start_date - 5 days to the display_time field. Then you could query:

ModelName.where("display_time > ?", Time.now)

This would be a clean way to deal with this problem!

edited Jan 13, 2012 at 14:28

answered Jan 13, 2012 at 14:22

davidb

8,9645 gold badges40 silver badges72 bronze badges

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

7 Comments

Elliot Over a year ago

thanks for the reply. I'm getting the following error undefined method days' for "7":String`

davidb Over a year ago

Instead of self.display_time=self.start_time-days_before_start.days write self.display_time=self.start_time-days_before_start.to_i.days

Elliot Over a year ago

Am now seeing this error:

You have a nil object when you didn't expect it! You might have expected an instance of Array. The error occurred while evaluating nil.-

Elliot Over a year ago

Parameters: {"utf8"=>"✓", "authenticity_token"=>"wb7acGREdNH2aeuYjye51wwaWdMXgTYvAcNxoDR9HAE=", "sale"=>{"start_time(3i)"=>"13", "start_time(2i)"=>"1", "start_time(1i)"=>"2012", "start_time(4i)"=>"23", "start_time(5i)"=>"00", "end_time(3i)"=>"14", "end_time(2i)"=>"1", "end_time(1i)"=>"2012", "end_time(4i)"=>"01", "end_time(5i)"=>"00",  "display_time_reader"=>"7"}, "commit"=>"create"}

davidb Over a year ago

that seems to be correct! Please post the whole code of the Model as an answer there must be sth. wrong!

|

Collectives™ on Stack Overflow

Rails ActiveRecord/SQL Query best practice

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related