Как бы вы сделали этот подзапрос Rails, используя Squeel?

Question

Я хочу реструктурировать запрос ниже, используя Squeel. Я хотел бы сделать это так, чтобы я мог связать операторы в нем и повторно использовать логику в разных частях запроса.

User.find_by_sql("SELECT 
    users.*,
    users.computed_metric,
    users.age_in_seconds,
    ( users.computed_metric / age_in_seconds) as compound_computed_metric
    from
    (
      select
        users.*,
        (users.id *2 ) as computed_metric,
        (extract(epoch from now()) - extract(epoch from users.created_at) ) as age_in_seconds
        from users
    ) as users")

Запрос должен работать в БД и не должен быть гибридным решением Ruby, поскольку он должен упорядочивать и разрезать миллионы записей.

Я поставил проблему так, чтобы она работала против нормального user и так, что вы можете играть с альтернативами ему.

Ограничения на приемлемый ответ

• запрос должен вернуть User объект со всеми нормальными атрибутами
• каждый пользовательский объект должен также включать extra_metric_we_care_about, age_in_seconds а также compound_computed_metric
• запрос не должен дублировать какую-либо логику, просто распечатывая строку в нескольких местах - я хочу избежать повторения одного и того же
• [обновлено] Запрос должен быть выполним в БД, чтобы результирующий набор, который может состоять из миллионов записей, можно было упорядочить и разрезать в БД перед возвратом в Rails
• [обновлено] Решение должно работать для БД Postgres

Пример типа решения, которое я хотел бы

Приведенное ниже решение не работает, но оно показывает тип элегантности, которого я надеюсь достичь

class User < ActiveRecord::Base
# this doesn't work - it just illustrates what I want to achieve

  def self.w_all_additional_metrics
    select{ ['*', 
              computed_metric, 
              age_in_seconds, 
              (computed_metric / age_in_seconds).as(compound_computed_metric)] 
      }.from{ User.w.compound_computed_metric.w_age_in_seconds }
  end

  def self.w_computed_metric
    where{ '(id *2 ) as computed_metric' }
  end

  def self.w_age_in_seconds
    where{ '(extract(epoch from now()) - extract(epoch from created_at) ) as age_in_seconds' }
  end
end

Вы должны быть в состоянии запустить это против вашей существующей базы данных

Обратите внимание, что я несколько придумал проблему, чтобы вы могли использовать свои существующие User класс и играть с ним в вашей консоли.

РЕДАКТИРОВАТЬ

1. БД, которую я использую - это Postgres.
2. Я не уверен, что на 100% ясно дал понять, что все запросы должны выполняться в БД. Это не может быть гибридным ответом, если некоторая логика по существу сделана в Rails. Это важно, так как я хочу иметь возможность упорядочивать и разрезать миллионы записей, используя вычисляемые столбцы.

Answer 1

У меня есть 2 решения в вашем случае. Моя база данных - mysql, и я упрощаю ваш код для демонстрации, я думаю, вы можете расширить его.

Первый - Squeel way, я смешал "sift" в Squeel и "from" в ActiveRecord Query. Я установил postgresql и только что проверил свое решение. Кажется, что вряд ли используются "squeel" и "epoch from" вместе, но я нашел альтернативный способ в postgresql, он называется "date_part". Я также изменил sql и уменьшил дублирование вычислений:

class User < ActiveRecord::Base           
  sifter :w_computed_metric do
    (id * 2).as(computed_metric)
  end

  sifter :w_age_in_seconds do
    (date_part('epoch' , now.func) - date_part('epoch', created_at)).as(age_in_seconds)
  end

  sifter :w_compound_computed_metric do
    (computed_metric / age_in_seconds).as(compound_computed_metric)
  end

  def self.subquery
    select{['*', sift(w_computed_metric) , sift(w_age_in_seconds)]}
  end

  def self.w_all_additional_metrics
    select{['*', sift(w_compound_computed_metric)]}.from("(#{subquery.to_sql}) users")
  end      
end

Это произвело sql:

SELECT *, "users"."computed_metric" / "users"."age_in_seconds" AS compound_computed_metric 
FROM (SELECT *, 
             "users"."id" * 2 AS computed_metric, 
             date_part('epoch', now()) - date_part('epoch', "users"."created_at") AS age_in_seconds FROM "users" 
     ) users

Вы можете проверить это с помощью консоли:

> User.w_all_additional_metrics.first.computed_metric
=> "2"
> User.w_all_additional_metrics.first.age_in_seconds
=> "633.136693954468"
> User.w_all_additional_metrics.first.compound_computed_metric
=> "0.00315887551471441"

Второй способ ActiveRecord, потому что ваш sql не очень сложен, так что вы можете связать его в ActiveRecord Query, этого достаточно для некоторых областей:

class User < ActiveRecord::Base
  scope :w_computed_metric, proc { select('id*2 as computed_metric') }
  scope :w_age_in_seconds, proc { select('extract (epoch from (now()-created_at)) as age_in_seconds') }
  scope :w_compound_computed_metric, proc { select('computed_metric/age_in_seconds as compound_computed_metric') }

  def self.subquery
    select('*').w_computed_metric.w_age_in_seconds
  end

  def self.w_all_additional_metrics
    subquery.w_compound_computed_metric.from("(#{subquery.to_sql}) users")
  end
end

Это производит следующий SQL:

SELECT 
  *, id*2 as computed_metric, 
  extract (epoch from (now()-created_at)) as age_in_seconds, 
  computed_metric / age_in_seconds as compound_computed_metric
FROM (
    SELECT 
      *, 
      id*2 as computed_metric, 
      extract (epoch from (now()-created_at)) as age_in_seconds 
    FROM 
      "users" 
    ) users 
ORDER BY compound_computed_metric DESC 
LIMIT 1

Надеюсь, что он отвечает вашим требованиям:)

Answer 2

Очень вероятно, что я совершенно не прав. Я чувствую, что вы слишком упрощаете свою проблему, чтобы позволить другим понять ее. Поскольку я не могу дать этот хорошо отформатированный код в комментарии, я ввожу ответ здесь.

SELECT 
    users.*,
    users.computed_metric,
    users.age_in_seconds,
    ( users.computed_metric / age_in_seconds) as compound_computed_metric
    from
    (
      select
        users.*,
        (users.id *2 ) as computed_metric,
        (extract(epoch from now()) - extract(epoch from users.created_at) ) as age_in_seconds
        from users
    ) as users

Ниже SQL эквивалентно вашему выше SQL. Вот почему я говорю, что подзапрос не нужен.

select
  users.*,
  (users.id *2 ) as computed_metric,
  (extract(epoch from now()) - extract(epoch from users.created_at) ) as age_in_seconds,
  computed_metric/age_in_seconds as compound_computed_metric
  from users

Если это так, то соединение_компуту_метрики можно рассчитать следующим образом. Пользовательский запрос не требуется.

class User < ActiveRecord::Base

  def compound_computed_metric
    computed_metric/age_in_seconds
  end
  def computed_metric
    self.id * 2
  end
  def age_in_seconds
    Time.now - self.created_at
  end
end

1.9.3p327 :001 > u = User.first
  User Load (0.1ms)  SELECT "users".* FROM "users" LIMIT 1
 => #<User id: 1, name: "spider", created_at: "2013-08-10 04:29:35", updated_at: "2013-08-10 04:29:35">
1.9.3p327 :002 > u.compound_computed_metric
 => 1.5815278998954843e-05
1.9.3p327 :003 > u.age_in_seconds
 => 126471.981447
1.9.3p327 :004 > u.computed_metric
 => 2

Answer 3

Let's preface this with it's not the answer you are looking for...

Теперь, с учетом этого, вот что я попробовал и как это связано с двумя ссылками, которые я разместил в комментариях к вопросу.

class User < ActiveRecord::Base
  # self-referential association - more on this later
  belongs_to :myself, class_name: "User", foreign_key: :id

  scope :w_computed_metric, ->() { select{[id, (id *2).as(computed_metric)]} }
  scope :w_age_in_seconds,  ->() { select{[id, (extract('epoch from now()') - extract('epoch from users.created_at')).as(age_in_seconds)]} }
  scope :w_default_attributes, ->() { select{`*`} }

  def self.compound_metric
    scope = User.w_default_attributes.select{(b.age_in_seconds / a.computed_metric).as(compound_metric)}
    scope = scope.joins{"inner join (" + User.w_computed_metric.to_sql + ") as a on a.id = users.id"}
    scope = scope.joins{"inner join (" + User.w_age_in_seconds.to_sql + ") as b on b.id = users.id"}
  end

  sifter :sift_computed_metric do
    (id * 2).as(computed_metric)
  end

  sifter :sift_age_in_seconds do
    (extract(`epoch from now()`) - extract(`epoch from users.created_at`)).as(age_in_seconds)
  end

  def self.using_sifters_in_select
    User.w_default_attributes.joins{myself}.select{[(myself.sift :sift_computed_metric), (myself.sift :sift_age_in_seconds)]}
  end

  def self.using_from
    scope = User.w_default_attributes
    scope = scope.select{[(age_in_seconds / computed_metric).as(compound_metric)]}
    scope = scope.from{User.w_computed_metric.w_age_in_seconds}
  end
end

Итак, бег User.compound_metric в консоли выдаст результаты, которые вы ищете - User объект с дополнительными атрибутами: computed_metric, age_in_seconds, а также compound_metric, К сожалению, это нарушает третье ограничение, которое вы наложили на приемлемый ответ. Ну что ж...

Я также попробовал несколько других вещей (как вы можете видеть сверху):

Первое, на что следует обратить внимание, - это ассоциация самоссылки, которой я очень горжусь, хотя она и не дает нам того, куда мы хотим идти.

belongs_to :myself, class_name: "User", foreign_key: :id

Этот отличный кусок кода позволяет вам получить доступ к тому же объекту через соединение. Почему это важно? Ну, Squeel позволит вам получить доступ к ассоциациям только через joins{} метод, если вы не передадите ему строку SQL. Это позволяет нам использовать sifterОсобенность Squeel - в этом случае не фильтровать результаты, а включить совокупные столбцы из БД и позволить Squeel выполнять тяжелую работу по наложению псевдонимов и объединению операторов. Вы можете проверить это с

def self.using_sifters_in_select
  User.w_default_attributes.joins{myself}.select{[(myself.sift :sift_computed_metric), (myself.sift :sift_age_in_seconds)]}
end

Прелесть просеивателей заключается в том, что они обладают цепной способностью и синтетическим сахаром - они очень плоские и удобочитаемые.

Последнее, с чем я пытался играть, это .from{}, До этого вопроса я даже не знал, что он существует. Я был очень взволнован возможностью того, что я упустил что-то такое простое, как включение источника для запроса (в данном случае суб-выбора). Тестирование с использованием

def self.using_from
    scope = User.w_default_attributes
    scope = scope.select{[(age_in_seconds / computed_metric).as(compound_metric)]}
    scope = scope.from{User.w_computed_metric.w_age_in_seconds}
end

приводит к ошибке TypeError:

TypeError: Cannot visit Arel::SelectManager
  from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:28:in `rescue in visit'
  from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:19:in `visit'
  from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:348:in `visit_Arel_Nodes_JoinSource'
  from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:21:in `visit'
  from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:139:in `visit_Arel_Nodes_SelectCore'
  from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:121:in `block in visit_Arel_Nodes_SelectStatement'
  from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:121:in `map'
  from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:121:in `visit_Arel_Nodes_SelectStatement'
  from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:21:in `visit'
  from /home/prg10itd/projects/arel/lib/arel/visitors/visitor.rb:5:in `accept'
  from /home/prg10itd/projects/arel/lib/arel/visitors/to_sql.rb:19:in `accept'

(и да, я тестирую против локальной копии Arel и Squeel). Я недостаточно знаком с внутренней работой Арела, чтобы решить проблему без дальнейших усилий (и, скорее всего, форка Арела). Похоже, что Squeel просто проходит from{} Метод Ареля from() метод, ничего не делая (кроме остальной магии, которая является Squeel).

Так, где это оставляет нас? Решение, которое работает, но не так красиво и элегантно, как хотелось бы, но, возможно, кто-то другой может использовать это для лучшего решения.

PS - это с Rails v3.2.13 и соответствующей версией Arel. Исходники для Rails v4 и Arel довольно разные и не проверены на это.