CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlruby-on-rails-4
007sumit
007sumit

Reputation: 51

Rails 4 where and select query with custom function

I am using Rails 4 and is having a table structure something like this:

Order(id: integer, ..., created_at: datetime, states_time: string)

Now I want to query the above table based on states_time, forex: to find all orders which are dispatched between time t1 and t2. Given, dispatch time can be computed using a function get_dispatch_time(x).

Hopefully, final where query should be like:

Order.all.where(get_dipatch_time: t1..t2) # WRONG SYNTAX

similar as:

Order.all.where(created_at: t1..t2)

I can fetch this data using select query and lamda expression, but then again issue is it will return array of ActiveRecord Objects, instead of an ActiveRecord::Relation, and hence further queries (pagination query) can not be executed over it.

Order.all.select{ |o| get_dispatch_time(o) > t1 && get_dispatch_time(o) < t2 }
=> #[#<Order id: 10, ship_by: ....>, #<Order id: 2, ..> ]

But, instead the required result should be:

=> #<ActiveRecord::Relation [#<Order id: 10, ship_by: ....>, #<Order id: 2, ..> ]>

The main issue is one can chain queries only over ActiveRecord::Relation. Forex: Order.where(...).order(...).sort(...).limit(...)

Can someone help me with:

  1. How to use custom methods with where query, returning ActiveRecord::Relation? OR
  2. How to apply query chain with select query in between ?

EDIT

Some clarifications:

  1. get_dispatch_time(x) is a Ruby function.

  2. I need to filter the data based on some custom function f(x), where x is the column name. Forex: if column name x is execution_date:DateTime,the f(x) can be something like 2^((execution_date * 1000) + 200), just some complex function (and yeah it is really complex !!). So, now want to query based on that complex function return value (which is a DateTime)

  3. Making query on a column name is easy, my question is How to make query on a custom function. (It is possible for select query by using lambda expression, as I mentioned above. But Is it possible for where query too ? Is there any other way to solve above problem?)

Upvotes: 2

Views: 2031

Answers (1)

axvm
axvm

Reputation: 2113

You don't need sql functions if you don't know how to use it.

forex: to find all orders which are dispatched between time t1 and t2

For example you have model Foo with column execution_date:date. To find all records in foos table which has execution_date in range of t1 and t2 you must to use next query:

Foo.where('execution_date > ? OR execution date < ?', t1, t2)

This query means 'I am looking for records in foos table which have date in execution_date more than t1 OR less than t2'

Upvotes: 1

Related Questions