There is such a sample:

o = Otchet.where("issue_number = ?", 118799) => #<ActiveRecord::Relation [#<Otchet id: 2, issue_number: 118799, assigned_to_id: 279, created_at: "2016-08-24 11:53:52", updated_at: "2016-08-24 11:53:52">, #<Otchet id: 3, issue_number: 118799, assigned_to_id: 1236, created_at: "2016-08-24 11:54:33", updated_at: "2016-08-24 11:54:33">, #<Otchet id: 4, issue_number: 118799, assigned_to_id: 2743, created_at: "2016-08-24 12:18:07", updated_at: "2016-08-24 12:18:07">]>

Tell me how to process this sample so that the output calculates the time between created_at. Those. in this case there are 3 elements that have:

 created_at1: "2016-08-24 11:53:52" created_at2: "2016-08-24 11:54:33" created_at3: "2016-08-24 12:18:07"

Need to get the difference: created_at2 - created_at1, created_at3 - created_at2, Time.now - created_at3 . Tell me how can this be done?

  • Heavy. It is not even beautifully written in SQL. ActiveRecord'ov query language here, most likely, he does not pull. - D-side
  • @ D-side, I don’t need to use ActiveRecord, I just chose the data for them, and then I thought something like this: o.each do |time| какая-то логика end o.each do |time| какая-то логика end - S.Ivanov
  • M ... understood you. Although it may not be good to think that it is not a base, it may be worse to consider a base ._. - D-side
  • @ D-side, do not tell me how this can be done? - S.Ivanov

2 answers 2

All the ways to consider it at the database level that I can imagine are scary, slow, or unreliable. Let's try at the level of Ruby.

ActiveRecord::Relation is Enumerable . That is why it has the Enumerable#each_cons(n) method ( each consecutive , each consecutive), transmitting to its block full sets of n elements (if the elements are smaller, the set is not issued), moving forward one by one with each step.

The array is also Enumerable , and the example on it (from the documentation) is quite clear:

 (1..10).each_cons(3) { |a| pa } # outputs below [1, 2, 3] [2, 3, 4] [3, 4, 5] [4, 5, 6] [5, 6, 7] [6, 7, 8] [7, 8, 9] [8, 9, 10]

By making .each_cons(2) you get an iterator over consecutive pairs of objects. Since the block argument will always have an array, it can be decomposed (destructure) into separate variables:

 [1, 4, 9, 16].each_cons(2) do |(before, after)| # внимание, круглые скобки # Пара круглых скобок в списке аргументов чего бы там ни было раскладывает одно значение. # ...если это массив или его .to_ary возвращает массив. Здесь мы знаем размер массива и # что в нём точно есть 2 элемента, но если бы мы не были уверены, вместо недостающих # был бы nil, а лишние бы попросту потерялись. puts("#{after} - #{before} = #{after - before}") end # 4 - 1 = 3 # 9 - 4 = 5 # 16 - 9 = 7 # => nil

This method can also be applied to ActiveRecord::Relation , getting sets from individual records. Forward!

... just do not forget that for N objects you will have N - 1 differences. How to handle this is your business.

    In addition, I would still suggest a traditional SQL solution, starting from the following SQL query

     SELECT fst.id, fst.created_at - snd.created_at AS diff FROM otchets AS fst LEFT JOIN otchets AS snd ON fst.id = snd.id + 1 ORDER BY fst.id;

    To do this, enter the virtual field diff in the model report, in which we will store the difference between the previous and subsequent time intervals

     class Otchet < ActiveRecord::Base ... def diff self[:diff] end ... end

    Then the above SQL query can be obtained as follows.

     col = Otchet .select('otchets.id, otchets.created_at - snd.created_at AS diff') .joins('LEFT JOIN otchets AS snd ON otchets.id = snd.id + 1') .order('otchets.id') p col.collect(&:diff) => [nil, "00:00:00.009453", "00:00:00.009968"]

    Unfortunately, the solution presented above is intended only for a sequential and inseparable sequence of id identifiers. You can get around this by using MySQL variables (window functions, unfortunately, MySQL does not support). Unfortunately, the resulting query is rather "multi-storied", probably there is no point in translating it into ActiveRecord (unless it is used as a pure SQL query).

     SELECT map.id, map.prev_id, lft.created_at - rgt.created_at AS diff FROM (SELECT @prev_id AS prev_id, (@prev_id := id) AS id FROM otchets, (SELECT @prev_id := null) AS var_init) AS map LEFT JOIN otchets AS lft ON lft.id = map.id LEFT JOIN otchets AS rgt ON rgt.id = map.prev_id WHERE map.prev_id IS NOT NULL;
    • ... and it calculates that the numbering id will be consistent and continuous. This restriction is worth mentioning. - D-side
    • @ D-side Yes, there is such a thing, I will try to think more and present a more universal solution. If I fail to add your remark to the decision. - cheops
    • I once looked for him too. The best thing I found: LAG from MSSQL , which allows you to refer back. - D-side
    • With the help of window functions, it is possible to taxi almost any such task, it’s a pity in MySQL that they don’t exist and probably will hardly appear. - cheops
    • @cheops, thanks for your decision, but I'd rather use the implementation on ruby - S.Ivanov