0

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
user1185081
  • 1,898
  • 2
  • 21
  • 46
  • If you want to extract the week in the database you want to use `extract('week', skills.created_at)`. See https://stackoverflow.com/questions/9304724/how-do-i-use-functions-like-concat-etc-in-arel – max Aug 26 '20 at 12:30

1 Answers1

0

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]]
)
Siim Liiser
  • 3,860
  • 11
  • 13
  • Yes, this works fine, but I was looking for something closer to Ruby, independant from RDBMS. May be a method in the model would do ... – user1185081 Aug 27 '20 at 13:04