CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
pyfl88
pyfl88

Reputation: 1710

Using postgres to search for some data

I am using pg and need to get some datas from db to create a chart on my page.

Here is the table schema:

t.decimal  "balance",     precision: 16, scale: 2
t.datetime "created_at",                           null: false
t.datetime "updated_at",                           null: false

This is an example of the datas

[#<Account:0x007f8a58b8aee0
  id: 1,
  balance: 5000,
  created_at: Sun, 16 Nov 2015 02:27:43 UTC +00:00,
  updated_at: Sun, 16 Nov 2015 02:27:43 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8aaf8
  id: 2,
  balance: 4000,
  created_at: Sun, 16 Nov 2015 15:05:07 UTC +00:00,
  updated_at: Sun, 16 Nov 2015 15:05:07 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8a7d8
  id: 3,
  balance: 3000,
  created_at: Sun, 15 Nov 2015 15:07:04 UTC +00:00,
  updated_at: Sun, 15 Nov 2015 15:07:04 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8a508
  id: 4,
  balance: 2000,
  created_at: Sun, 15 Nov 2015 15:12:32 UTC +00:00,
  updated_at: Sun, 15 Nov 2015 15:12:32 UTC +00:00,
  order_id: nil>,
 #<Account:0x007f8a58b8a148
  id: 5,
  balance: 1800,
  created_at: Sun, 13 Nov 2015 15:14:14 UTC +00:00,
  updated_at: Sun, 13 Nov 2015 15:14:14 UTC +00:00,
  order_id: nil>]

Say for example below is the desire result I want to get:

[4000, 2000, 1800, 1800, 1800]

The conditions for the results are: 1) I want to get the result for last 5 days, so the array will always have 5 data. 2) I have to get the latest balance for the day. 3) If any of the day there is no transaction, the balance will be equal to the balance of the day before.

UPDATE:

I tried to get array of hashes from the datas like this:

Account.where("created_at > ?", Date.today - 5).map{|x| {x.created_at => x.balance}}

And get the following results:

[{Sun, 16 Nov 2015 02:27:43 UTC +00:00=>
   5000},
 {Sun, 16 Nov 2015 15:05:07 UTC +00:00=>
   4000},
 {Sun, 15 Nov 2015 15:07:04 UTC +00:00=>
   3000},
 {Sun, 15 Nov 2015 15:12:32 UTC +00:00=>
   2000},
 {Sun, 13 Nov 2015 15:14:14 UTC +00:00=>
   1800}]

I think I am on the right path, now I have to merge the keys which have the same date, and get the value of the latest time on that date. Any idea how I can do this? Thanks.

Upvotes: 0

Views: 52

Answers (1)

mabe02
mabe02

Reputation: 2734

The conditions for the results are: 1) I want to get the result for last 5 days, so the array will always have 5 data. 2) I have to get the latest balance for the day. 3) If any of the day there is no transaction, the balance will be equal to the balance of the day before.

Get the result for the last 5 days:

five_days_ago = Date.today - 5 # you can make a date diff just subtracting the number of days
Account.where("created_at > ?", five_days_ago)

Get the latest balance for each and every day: you should get the balance by date and get the one where the created_at is the max. However this might be easier to do in Ruby processing your output:

require 'date'

five_days_ago = Date.today - 5

query = Account.where("created_at > ?", Date.today - 5).map{|x| {x.created_at => x.balance}}
query = query.reduce({}, :merge) # reduce to one hash

# Create an hash with the dates and the latest created_at
dates = query.group_by{|k, v| Date.parse(k.to_s)}
dates.map{|k, v| {Date.parse(v.max.to_s) => v.max }

# Create an output hash including all dates
result = {}
last_available_balance = 0
(five_days_ago...Date.today).each do |d|

  if dates.keys.includes? d
    # assign the value corresponding to the latest timestamp
    last_available_balance = query[dates[d]]
    result[d] = last_available_balance

  else
    # keep the value of the last available balance
    result[d] = last_available_balance

  end
end

Upvotes: 2

Related Questions