CODEWITHSUNDEEP
rubysequel
dmitq
dmitq

Reputation: 137

More complicated Sequel selections

There is a simple database in Sequel:

DB = Sequel.sqlite

DB.create_table :items do
    primary_key :id
    DateTime :date
    String :name
end

items = DB[:items]
items.insert(:name => 'abc', :date => DateTime.now)
items.insert(:name => 'ghi', :date => DateTime.now)
items.insert(:name => 'def', :date => DateTime.now)

The question: is it a good idea to store 'strange' objects in database, like DateTime?

puts items.first(:name => 'ghi')[:date].year

Outputs '2010' so, well - it works. But still i'm quite curious about it. If it's nothing bad, what about filtering? Things like that:

puts items.first(:date.year => 2010)[:name]

... won't work. Is it possible to do it in other way? How?

Upvotes: 1

Views: 774

Answers (2)

bjg
bjg

Reputation: 7477

I think you're asking 2 different questions here:

  1. It's absolutely normal to store rich objects like dates in relational databases (most if not all support dates and the like). Sequel's DateTime attribute type is acknowledging this fact and provides an abstraction over all its supported back-ends.

  2. Filtering is less obvious. Different back-ends (read database implementations) will provide very different ways of decomposing and hence selecting parts of these objects. An ORM like Sequel has to draw some line of abstraction which is (hopefully) commonly applicable to all the supported back-ends. In some cases (and DateTime may well be one of them) the more sophisticated filtering will not be available via the ORM's syntactic sugar. You may, in such cases, need to drop down to the per-database SQL facility to achieve what you want, or at the very least, be aware of the underlying semantics when forming your syntactic sugar solution

Upvotes: 1

Jeremy Evans
Jeremy Evans

Reputation: 12139

bjg is correct. For your specific situation, instead of:

puts items.first(:date.year => 2010)[:name]

You could try:

puts items.first(:date.extract(:year) => 2010)[:name]

Which should work if your database supports the SQL standard extract function (not all databases do). If your database does not, you'll have to call your whatever similar function exists for your database.

Upvotes: 2

Related Questions