How is Arel # extraction method used in rails?
I am trying to use Arel # extract method. I saw an example in the test case in test_extract.rb in the source, but when I try to reproduce it in my application, I get undefined method.
table = Arel::Table.new :users
puts table[:created_at].extract('hour').to_sql
=> NoMethodError: undefined method `extract' for #<Arel::Attributes::Attribute:0x7..8>
I am using pg as my database.
Update:
My goal is to end up with this result in sql:
SELECT users.* FROM users WHERE EXTRACT(HOUR from users."created_at") = '1'
I would like to find all users who were created in an hour equal to one of the days. This works in sql, but I'm wondering how to create it in isl. Below is an example of how it is used in a test suite.
source to share
extract is a node method, you can overlay it on any column (eg users [: id]), but not on an Arel :: Table instance.
So, to create your request, you need:
- get Arel :: Table instance
users = Arel::Table.new(:users)
or if you are using ActiveRecord -users = User.arel_table
- set SELECT statement in Arel :: Table file using method
project
:users = users.project(Arel.sql('*'))
- set WHERE clause with method
where
:users.where(users[:created_at].extract(:hour).eq(1))
In one block:
query = User.arel_table.
project(Arel.sql('*')).
where(users[:created_at].extract(:hour).eq(1))
users = User.find_by_sql(query)
# => "SELECT * FROM \"users\" WHERE EXTRACT(HOUR FROM \"users\".\"created_at\") = 1"
source to share
I had to do DOW extraction on an Arel :: Nodes :: NamedFunction instance, which does not provide the #extract method (as in Arel 6.0). I managed to achieve this by manually creating an instance Arel::Nodes::Extract
. Here's what worked for me in case anyone has a similar problem:
Arel::Nodes::Extract.new(
Arel::Nodes::NamedFunction.new('some_function_name', [param1, param2, ...]),
:dow
)
You can use Arel node directly with ActiveRecord #where
instead of building a full query through Arel as shown in Alexander Carmes' answer. So here's another way to fulfill the request required by the response:
User.where(
Arel::Nodes::Extract.new(User.arel_table[:created_at], :hour).eq(1)
)
What gives:
SELECT "users".* FROM "users" WHERE EXTRACT(HOUR FROM "users"."created_at") = 1
With the added benefit of being able to chain other areas defined in your model User
.
source to share