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

Question

I use Arel to build reusable and structured queries, but reading around, I didn't find a clear and efficient way to alter extracted date to actually retrieve the calendar week.

Using cweek Ruby method, I am trying to build the following query (on Postgres):

week_series = Skill.joins(concepts_list).select(skills[:created_at].cweek, skills[:id].count.as("count")).group(skills[:created_at].cweek)

Here is my base query upon skills:

### Base object
def skills
  Skill.arel_table
end

# Additional tables
def users
  User.arel_table
end

def organisations
  Organisation.arel_table
end

def themes
  Playground.arel_table.alias('themes')
end

def domains
  BusinessArea.arel_table.alias('domains')
end

def collections
  BusinessObject.arel_table.alias('collections')
end

# Queries
def concepts_list
  skills.
  join(users).on(skills[:owner_id].eq(users[:id])).
  join(organisations).on(skills[:organisation_id].eq(organisations[:id])).
  join(collections).on(skills[:business_object_id].eq(collections[:id])).
  join(domains).on(collections[:parent_id].eq(domains[:id]).and(collections[:parent_type].eq('BusinessArea'))).
  join(themes).on(domains[:playground_id].eq(themes[:id])).
  join_sources
end

def concepts_list_output
  [skills[:id], skills[:created_at], users[:user_name], users[:name],
  organisations[:code], themes[:code], domains[:code]]
end

Answer 1

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]]
)