How to Use Functions Like Concat(), etc. in Arel

How do I use functions like CONCAT(), etc. in ARel?

Use NamedFunction:

name = Arel::Attribute.new(Arel::Table.new(:countries), :name)
func = Arel::Nodes::NamedFunction.new 'zomg', [name]
Country.select([name, func]).to_sql

Using `CONCAT` w/ Arel in Rails 4

With the latest Arel it's required to use Arel::Nodes.build_quoted(' ') instead of just String (' '). So the answer nowadays is:

SEPARATOR = Arel::Nodes.build_quoted(' ')

Arel::Nodes::NamedFunction.new(
  'concat',
  [arel_table[:first_name], SEPARATOR, arel_table[:last_name]]
)

How do you create and use functions in Arel that are not bound to a column?

You can use Arel::Nodes::NamedFunction.

This class initializes with the name of the function and an arguments array. The Vistor for a NamedFunction will assemble the SQL as FUNCTION_NAME(comma, separated, arguments).

Since NOW has no arguments all we need to do is pass an empty arguments array.

fn = Arel::Nodes::NamedFunction.new("NOW",[]) 
memberships = Arel::Table.new("memberships")
memberships.project(Arel.star).where(
  memberships[:expires].lt(fn)
).to_sql 
#=> "SELECT * FROM [memberships] WHERE [memberships].[expires] < NOW()"

Function appears to be a super class for inheritance purposes only, children include Sum,Exists,Min,Max,Avg, and NamedFunction (which allows you to call any other function by name as shown above)

Can't group by result of a named function

This is a tricky one. First of all, your NamedFunction definition has a couple, totally non-obvious issues: first, the first argument needs to be a string, or else you get this:

irb(main):040:0> User.group( Arel::Nodes::NamedFunction.new(:date, [User.arel_table[:created_at]], 'date') )
TypeError: no implicit conversion of Symbol into String
        from /var/lib/gems/2.1.0/gems/arel-6.0.2/lib/arel/collectors/plain_string.rb:13:in `<<'
        from /var/lib/gems/2.1.0/gems/arel-6.0.2/lib/arel/visitors/to_sql.rb:458:in `visit_Arel_Nodes_NamedFunction'

Secondly, you need to drop the third argument (the alias), or else Arel tries to shove it into the GROUP BY clause and you get an error (at least in Postgres):

irb(main):045:0> User.group( Arel::Nodes::NamedFunction.new('date', [User.arel_table[:created_at]], 'date') )
  User Load (4.9ms)  SELECT "users".* FROM "users" GROUP BY date("users"."created_at") AS date
PG::SyntaxError: ERROR:  syntax error at or near "AS"
LINE 1: ...".* FROM "users" GROUP BY date("users"."created_at") AS date
                                                                ^
: SELECT "users".* FROM "users" GROUP BY date("users"."created_at") AS date
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "AS"
LINE 1: ...".* FROM "users" GROUP BY date("users"."created_at") AS date
                                                                ^
: SELECT "users".* FROM "users" GROUP BY date("users"."created_at") AS date
        from /var/lib/gems/2.1.0/gems/activerecord-4.2.3/lib/active_record/connection_adapters/postgresql_adapter.rb:596:in `async_exec'

You haven't noticed these because the error you're getting is actually related to the .count call; without it, this actually works now:

irb(main):066:0> nf = Arel::Nodes::NamedFunction.new('date', [User.arel_table[:created_at]])
=> #<Arel::Nodes::NamedFunction:0x9dc9ca0 @expressions=[#<struct Arel::Attributes::Attribute relation=#<Arel::Table:0xbdb689c @name="users", @engine=User(id: integer, name: string, age: integer, created_at: datetime, updated_at: datetime), @columns=nil, @aliases=[], @table_alias=nil, @primary_key=nil>, name=:created_at>], @alias=nil, @distinct=false, @name="date">
irb(main):067:0> User.group(nf).select(nf)
  User Load (2.5ms)  SELECT date("users"."created_at") FROM "users" GROUP BY date("users"."created_at")
=> #<ActiveRecord::Relation [#<User id: nil>]>

So, why doesn't .count work? Unfortunately, following the stack trace, this just looks like a bug. The count method goes into ActiveRelation::Calculations#execute_grouped_calculation and simply goes down a bad path. There's no support in there for handling a NamedFunction from Arel.

However, you can work around it:

irb(main):017:0> User.group(nf).pluck('count(*)').first
   (2.5ms)  SELECT count(*) FROM "users" GROUP BY date("users"."created_at")
=> 1

So... yeah. You might want to open an Issue for this in Rails. I'd personally just suggest you group by the string expression and save yourself some headache!

How to use `to_sql` in AREL when using `average()`?

The reason this is happening is because the average method is on ActiveRecord::Relation, not Arel, which forces the computation.

m = Review.where('id = ?', 42).method(:average)
#=> #<Method: ActiveRecord::Relation(ActiveRecord::Calculations)#average>
m.source_location  # or m.__file__ if you're on a different version of Ruby
#=> ["/Users/jtran/.rvm/gems/ruby-1.9.2-p0/gems/activerecord-3.0.4/lib/active_record/relation/calculations.rb", 65]

By checking out the internals of ActiveRecord::Calculations, you can derive how to get at the SQL that it uses.

my_reviewed_user_id = 42
relation = Review.where('reviewed_user_id = ?', my_reviewed_user_id)
column = Arel::Attribute.new(Review.unscoped.table, :stars)
relation.select_values = [column.average]
relation.to_sql
#=> "SELECT AVG(\"reviews\".\"stars\") AS avg_id FROM \"reviews\" WHERE (reviewed_user_id = 42)"

Careful if you're working at the console. ActiveRecord::Relation caches things so if you type the above into the console line by line, it will actually not work, because pretty-printing forces the relation. Separating the above by semicolons and no new lines, however, will work.

Alternatively, you can use Arel directly, like so:

my_reviewed_user_id = 42
reviews = Arel::Table.new(:reviews)
reviews.where(reviews[:reviewed_user_id].eq(my_reviewed_user_id)).project(reviews[:stars].average).to_sql
#=> "SELECT AVG(\"reviews\".\"stars\") AS avg_id FROM \"reviews\" WHERE \"users\".\"reviewed_user_id\" = 42"

How to extract a week from a date in Arel query with Rails 5.2?

The equivalent for ruby cweek in postgresql is EXTRACT/DATE_PART. Might differ a bit if you're using a different database.

The sql you're after is

DATE_PART('week', "skills"."created_at")

That is just a NamedFunction

Arel::Nodes::NamedFunction.new(
  'DATE_PART',
  [Arel::Nodes.build_quoted('week'), skills[:created_at]]
)

Dynamic query builder stack too deep for Arel query Rails

my solution finally is:

my_big_set = Set.new(big_array)
subquery = '(condition = ? and condition_two = ?)'
query = Array.new(my_big_set, subquery).join(' OR ')
query_values = my_big_set.map do |values_to_check|
   [values_to_check[:first], values_to_check[:two]]
end.flatten

where(query, *query_values).update_all(field_to_update: true)

that way, we construct:

1. the SQL query
2. the values to pass to where()
3. we still use active record where() in order to be protected from injection etc...

And this fixes the limit!

Is there a way to apply a moving limit in SQL

You need to tag your question with the brand of RDBMS you're using. Frequently for Rails developers, they're using MySQL, but the answer to your question depends on this.

For all brands except for MySQL, the correct and standard solution is to use windowing functions:

SELECT * FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY day) AS RN, *
  FROM stockmarketdata
) AS t
WHERE t.RN = 1;

For MySQL, which doesn't support windowing functions yet, you can simulate them in a kind of clumsy way with session variables:

SELECT * FROM (SELECT @day:=0, @r:=0) AS _init,
(
  SELECT IF(day=@day, @r:=@r+1, @r:=0) AS RN, @day:=day AS d, *
  FROM stockmarketdata
) AS t
WHERE t.RN = 1

---

Related Topics