Mixing
Active records daily count array w.r.t created_at or updated_at
up vote
1
down vote
favorite
I am working on a school project where I have tables class_standard and enrollments
enrollments belongs_to class_standard and class_standard has many enrollments.
enrollments are created_at in different days. I want to get daily counts of enrollments in last 30 days in an array.
i.e. [# of enrollments 1st day, 2nd day, 3rd day, .....]
ruby-on-rails activerecord rails-activerecord
asked yesterday
Uzair Nadeem
2081417
add a comment |
up vote
1
down vote
favorite
I am working on a school project where I have tables class_standard and enrollments
enrollments belongs_to class_standard and class_standard has many enrollments.
enrollments are created_at in different days. I want to get daily counts of enrollments in last 30 days in an array.
i.e. [# of enrollments 1st day, 2nd day, 3rd day, .....]
ruby-on-rails activerecord rails-activerecord
asked yesterday
Uzair Nadeem
2081417
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
I am working on a school project where I have tables class_standard and enrollments
enrollments belongs_to class_standard and class_standard has many enrollments.
enrollments are created_at in different days. I want to get daily counts of enrollments in last 30 days in an array.
i.e. [# of enrollments 1st day, 2nd day, 3rd day, .....]
ruby-on-rails activerecord rails-activerecord
asked yesterday
Uzair Nadeem
2081417
I am working on a school project where I have tables class_standard and enrollments
enrollments belongs_to class_standard and class_standard has many enrollments.
enrollments are created_at in different days. I want to get daily counts of enrollments in last 30 days in an array.
i.e. [# of enrollments 1st day, 2nd day, 3rd day, .....]
ruby-on-rails activerecord rails-activerecord
ruby-on-rails activerecord rails-activerecord
asked yesterday
Uzair Nadeem
2081417
asked yesterday
Uzair Nadeem
2081417
asked yesterday
Uzair Nadeem
2081417
asked yesterday
Uzair Nadeem
2081417
asked yesterday
Uzair Nadeem
2081417
2081417
add a comment |
add a comment |
3 Answers
3
active
oldest
votes
up vote
0
down vote
This is the sample of the code that gets the daily count of Enrollments created and stores them in an array.
arr = #initialize an array
(0..29).each do |x| #run a loop for 30 days
date = Time.now - x.days #get a specific day
arr << Enrollment.where(created_at: date.midnight..date.end_of_day).count #get count of enrollments on a specific day and add this to your array
end
arr = arr.reverse #reverse the array, because you want the today's count as last item of the array
answered yesterday
Saqib Shahzad
534119
Thats very inefficient as it will create n number of count queries.
– max
yesterday
add a comment |
up vote
0
down vote
What you want to do is select a count grouped by day. How exactly to achieve this depends on what database is used.
Postgres:
date_trunc('day', created_at)
MySQL:
date_format(created_at, '%Y-%m-%d')
For other db's check the documentation for date functions.
Altogether the query you want is something like this:
SELECT
count(*) AS count,
date_trunc('day', created_at) AS day
FROM "enrollments"
WHERE (created_at >= ?) -- one month ago
GROUP BY day
ORDER BY day
We can write this in ActiveRecord as:
Enrollment.select("count(*) AS count, to_char(created_at, 'YYYY-MM-DD') AS day")
.group("day")
.order("day")
.where(created_at: (1.month.ago..Time.current))
But this gives an ActiveRecord::Relation of records which is not very useful. To get the raw query values we want to use #select_all.
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
results = Enrollment.connection.select_all(sql)
When you convert the results to an array you will get an array of hashes:
[{"count"=>10, "day"=>"2018-10-20 00:00:00"}, {"count"=>10, "day"=>"2018-10-21 00:00:00"}, {"count"=>10, "day"=>"2018-10-22 00:00:00"}, {"count"=>10, "day"=>"2018-10-23 00:00:00"}, {"count"=>10, "day"=>"2018-10-24 00:00:00"}, {"count"=>10, "day"=>"2018-10-25 00:00:00"}, ...]
You can extract the count by mapping the array:
results.map {|r| r["count"] }
If you want the results just for one (or more) ClassStandards just add an additional condition to the where clause:
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.where(class_standard: @class_standard)
.to_sql
Or you call it off the association:
sql = @class_standard.enrollments
.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
answered yesterday
max
43.8k856103
date_trunc('day', created_at)anddate_format(created_at, '%Y-%m-%d')will return different results by the grouping will be the same.
– max
yesterday
add a comment |
up vote
0
down vote
If you want to get count of all days including the days where there isn't any enrollment:
daily_enrollments =
i = 29
while i >= 0
daily_enrollments >> Enrollments.where( created_at: Time.zone
.now
.beginning_of_day-i.day..
Time.zone
.now
.end_of_day-i.day).count
i = i-1
end
Output Example
[0,0,0,5,4,0,0,0,5,0,23,0,0,23,0,0,1,0,11,0,21,32,44,0,0,9,6,3,22,1]
This will give you an array of all 30 days. But this isn't seems an efficient code. This will run 30 queries for each record.
What seems better to me is group the records on created_at and get records for the days which have some records. i.e. excluding days with no record.
Enrollments.where(created_at: Time.current - 30.days..Time.current)
.group("date(created_at)")
.count
.sort
.to_h
Output Example
{Mon, 19 Nov 2018=>12}
answered yesterday
Uzair Nadeem
2081417
add a comment |
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
This is the sample of the code that gets the daily count of Enrollments created and stores them in an array.
arr = #initialize an array
(0..29).each do |x| #run a loop for 30 days
date = Time.now - x.days #get a specific day
arr << Enrollment.where(created_at: date.midnight..date.end_of_day).count #get count of enrollments on a specific day and add this to your array
end
arr = arr.reverse #reverse the array, because you want the today's count as last item of the array
answered yesterday
Saqib Shahzad
534119
Thats very inefficient as it will create n number of count queries.
– max
yesterday
add a comment |
up vote
0
down vote
This is the sample of the code that gets the daily count of Enrollments created and stores them in an array.
arr = #initialize an array
(0..29).each do |x| #run a loop for 30 days
date = Time.now - x.days #get a specific day
arr << Enrollment.where(created_at: date.midnight..date.end_of_day).count #get count of enrollments on a specific day and add this to your array
end
arr = arr.reverse #reverse the array, because you want the today's count as last item of the array
answered yesterday
Saqib Shahzad
534119
Thats very inefficient as it will create n number of count queries.
– max
yesterday
add a comment |
up vote
0
down vote
up vote
0
down vote
This is the sample of the code that gets the daily count of Enrollments created and stores them in an array.
arr = #initialize an array
(0..29).each do |x| #run a loop for 30 days
date = Time.now - x.days #get a specific day
arr << Enrollment.where(created_at: date.midnight..date.end_of_day).count #get count of enrollments on a specific day and add this to your array
end
arr = arr.reverse #reverse the array, because you want the today's count as last item of the array
answered yesterday
Saqib Shahzad
534119
This is the sample of the code that gets the daily count of Enrollments created and stores them in an array.
arr = #initialize an array
(0..29).each do |x| #run a loop for 30 days
date = Time.now - x.days #get a specific day
arr << Enrollment.where(created_at: date.midnight..date.end_of_day).count #get count of enrollments on a specific day and add this to your array
end
arr = arr.reverse #reverse the array, because you want the today's count as last item of the array
answered yesterday
Saqib Shahzad
534119
answered yesterday
Saqib Shahzad
534119
answered yesterday
Saqib Shahzad
534119
answered yesterday
Saqib Shahzad
534119
534119
Thats very inefficient as it will create n number of count queries.
– max
yesterday
add a comment |
Thats very inefficient as it will create n number of count queries.
– max
yesterday
Thats very inefficient as it will create n number of count queries.
– max
yesterday
Thats very inefficient as it will create n number of count queries.
– max
yesterday
add a comment |
up vote
0
down vote
What you want to do is select a count grouped by day. How exactly to achieve this depends on what database is used.
Postgres:
date_trunc('day', created_at)
MySQL:
date_format(created_at, '%Y-%m-%d')
For other db's check the documentation for date functions.
Altogether the query you want is something like this:
SELECT
count(*) AS count,
date_trunc('day', created_at) AS day
FROM "enrollments"
WHERE (created_at >= ?) -- one month ago
GROUP BY day
ORDER BY day
We can write this in ActiveRecord as:
Enrollment.select("count(*) AS count, to_char(created_at, 'YYYY-MM-DD') AS day")
.group("day")
.order("day")
.where(created_at: (1.month.ago..Time.current))
But this gives an ActiveRecord::Relation of records which is not very useful. To get the raw query values we want to use #select_all.
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
results = Enrollment.connection.select_all(sql)
When you convert the results to an array you will get an array of hashes:
[{"count"=>10, "day"=>"2018-10-20 00:00:00"}, {"count"=>10, "day"=>"2018-10-21 00:00:00"}, {"count"=>10, "day"=>"2018-10-22 00:00:00"}, {"count"=>10, "day"=>"2018-10-23 00:00:00"}, {"count"=>10, "day"=>"2018-10-24 00:00:00"}, {"count"=>10, "day"=>"2018-10-25 00:00:00"}, ...]
You can extract the count by mapping the array:
results.map {|r| r["count"] }
If you want the results just for one (or more) ClassStandards just add an additional condition to the where clause:
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.where(class_standard: @class_standard)
.to_sql
Or you call it off the association:
sql = @class_standard.enrollments
.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
answered yesterday
max
43.8k856103
date_trunc('day', created_at)anddate_format(created_at, '%Y-%m-%d')will return different results by the grouping will be the same.
– max
yesterday
add a comment |
up vote
0
down vote
What you want to do is select a count grouped by day. How exactly to achieve this depends on what database is used.
Postgres:
date_trunc('day', created_at)
MySQL:
date_format(created_at, '%Y-%m-%d')
For other db's check the documentation for date functions.
Altogether the query you want is something like this:
SELECT
count(*) AS count,
date_trunc('day', created_at) AS day
FROM "enrollments"
WHERE (created_at >= ?) -- one month ago
GROUP BY day
ORDER BY day
We can write this in ActiveRecord as:
Enrollment.select("count(*) AS count, to_char(created_at, 'YYYY-MM-DD') AS day")
.group("day")
.order("day")
.where(created_at: (1.month.ago..Time.current))
But this gives an ActiveRecord::Relation of records which is not very useful. To get the raw query values we want to use #select_all.
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
results = Enrollment.connection.select_all(sql)
When you convert the results to an array you will get an array of hashes:
[{"count"=>10, "day"=>"2018-10-20 00:00:00"}, {"count"=>10, "day"=>"2018-10-21 00:00:00"}, {"count"=>10, "day"=>"2018-10-22 00:00:00"}, {"count"=>10, "day"=>"2018-10-23 00:00:00"}, {"count"=>10, "day"=>"2018-10-24 00:00:00"}, {"count"=>10, "day"=>"2018-10-25 00:00:00"}, ...]
You can extract the count by mapping the array:
results.map {|r| r["count"] }
If you want the results just for one (or more) ClassStandards just add an additional condition to the where clause:
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.where(class_standard: @class_standard)
.to_sql
Or you call it off the association:
sql = @class_standard.enrollments
.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
answered yesterday
max
43.8k856103
date_trunc('day', created_at)anddate_format(created_at, '%Y-%m-%d')will return different results by the grouping will be the same.
– max
yesterday
add a comment |
up vote
0
down vote
up vote
0
down vote
What you want to do is select a count grouped by day. How exactly to achieve this depends on what database is used.
Postgres:
date_trunc('day', created_at)
MySQL:
date_format(created_at, '%Y-%m-%d')
For other db's check the documentation for date functions.
Altogether the query you want is something like this:
SELECT
count(*) AS count,
date_trunc('day', created_at) AS day
FROM "enrollments"
WHERE (created_at >= ?) -- one month ago
GROUP BY day
ORDER BY day
We can write this in ActiveRecord as:
Enrollment.select("count(*) AS count, to_char(created_at, 'YYYY-MM-DD') AS day")
.group("day")
.order("day")
.where(created_at: (1.month.ago..Time.current))
But this gives an ActiveRecord::Relation of records which is not very useful. To get the raw query values we want to use #select_all.
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
results = Enrollment.connection.select_all(sql)
When you convert the results to an array you will get an array of hashes:
[{"count"=>10, "day"=>"2018-10-20 00:00:00"}, {"count"=>10, "day"=>"2018-10-21 00:00:00"}, {"count"=>10, "day"=>"2018-10-22 00:00:00"}, {"count"=>10, "day"=>"2018-10-23 00:00:00"}, {"count"=>10, "day"=>"2018-10-24 00:00:00"}, {"count"=>10, "day"=>"2018-10-25 00:00:00"}, ...]
You can extract the count by mapping the array:
results.map {|r| r["count"] }
If you want the results just for one (or more) ClassStandards just add an additional condition to the where clause:
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.where(class_standard: @class_standard)
.to_sql
Or you call it off the association:
sql = @class_standard.enrollments
.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
answered yesterday
max
43.8k856103
What you want to do is select a count grouped by day. How exactly to achieve this depends on what database is used.
Postgres:
date_trunc('day', created_at)
MySQL:
date_format(created_at, '%Y-%m-%d')
For other db's check the documentation for date functions.
Altogether the query you want is something like this:
SELECT
count(*) AS count,
date_trunc('day', created_at) AS day
FROM "enrollments"
WHERE (created_at >= ?) -- one month ago
GROUP BY day
ORDER BY day
We can write this in ActiveRecord as:
Enrollment.select("count(*) AS count, to_char(created_at, 'YYYY-MM-DD') AS day")
.group("day")
.order("day")
.where(created_at: (1.month.ago..Time.current))
But this gives an ActiveRecord::Relation of records which is not very useful. To get the raw query values we want to use #select_all.
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
results = Enrollment.connection.select_all(sql)
When you convert the results to an array you will get an array of hashes:
[{"count"=>10, "day"=>"2018-10-20 00:00:00"}, {"count"=>10, "day"=>"2018-10-21 00:00:00"}, {"count"=>10, "day"=>"2018-10-22 00:00:00"}, {"count"=>10, "day"=>"2018-10-23 00:00:00"}, {"count"=>10, "day"=>"2018-10-24 00:00:00"}, {"count"=>10, "day"=>"2018-10-25 00:00:00"}, ...]
You can extract the count by mapping the array:
results.map {|r| r["count"] }
If you want the results just for one (or more) ClassStandards just add an additional condition to the where clause:
sql = Enrollment.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.where(class_standard: @class_standard)
.to_sql
Or you call it off the association:
sql = @class_standard.enrollments
.select("count(*) AS count, date_trunc('day', created_at) AS day")
.group("day")
.order("day")
.where("created_at >= ?", 1.month.ago)
.to_sql
answered yesterday
max
43.8k856103
edited yesterday
answered yesterday
max
43.8k856103
answered yesterday
max
43.8k856103
answered yesterday
max
43.8k856103
43.8k856103
date_trunc('day', created_at)anddate_format(created_at, '%Y-%m-%d')will return different results by the grouping will be the same.
– max
yesterday
add a comment |
date_trunc('day', created_at)anddate_format(created_at, '%Y-%m-%d')will return different results by the grouping will be the same.
– max
yesterday
date_trunc('day', created_at) and date_format(created_at, '%Y-%m-%d') will return different results by the grouping will be the same.– max
yesterday
date_trunc('day', created_at) and date_format(created_at, '%Y-%m-%d') will return different results by the grouping will be the same.– max
yesterday
add a comment |
up vote
0
down vote
If you want to get count of all days including the days where there isn't any enrollment:
daily_enrollments =
i = 29
while i >= 0
daily_enrollments >> Enrollments.where( created_at: Time.zone
.now
.beginning_of_day-i.day..
Time.zone
.now
.end_of_day-i.day).count
i = i-1
end
Output Example
[0,0,0,5,4,0,0,0,5,0,23,0,0,23,0,0,1,0,11,0,21,32,44,0,0,9,6,3,22,1]
This will give you an array of all 30 days. But this isn't seems an efficient code. This will run 30 queries for each record.
What seems better to me is group the records on created_at and get records for the days which have some records. i.e. excluding days with no record.
Enrollments.where(created_at: Time.current - 30.days..Time.current)
.group("date(created_at)")
.count
.sort
.to_h
Output Example
{Mon, 19 Nov 2018=>12}
answered yesterday
Uzair Nadeem
2081417
add a comment |
up vote
0
down vote
If you want to get count of all days including the days where there isn't any enrollment:
daily_enrollments =
i = 29
while i >= 0
daily_enrollments >> Enrollments.where( created_at: Time.zone
.now
.beginning_of_day-i.day..
Time.zone
.now
.end_of_day-i.day).count
i = i-1
end
Output Example
[0,0,0,5,4,0,0,0,5,0,23,0,0,23,0,0,1,0,11,0,21,32,44,0,0,9,6,3,22,1]
This will give you an array of all 30 days. But this isn't seems an efficient code. This will run 30 queries for each record.
What seems better to me is group the records on created_at and get records for the days which have some records. i.e. excluding days with no record.
Enrollments.where(created_at: Time.current - 30.days..Time.current)
.group("date(created_at)")
.count
.sort
.to_h
Output Example
{Mon, 19 Nov 2018=>12}
answered yesterday
Uzair Nadeem
2081417
add a comment |
up vote
0
down vote
up vote
0
down vote
If you want to get count of all days including the days where there isn't any enrollment:
daily_enrollments =
i = 29
while i >= 0
daily_enrollments >> Enrollments.where( created_at: Time.zone
.now
.beginning_of_day-i.day..
Time.zone
.now
.end_of_day-i.day).count
i = i-1
end
Output Example
[0,0,0,5,4,0,0,0,5,0,23,0,0,23,0,0,1,0,11,0,21,32,44,0,0,9,6,3,22,1]
This will give you an array of all 30 days. But this isn't seems an efficient code. This will run 30 queries for each record.
What seems better to me is group the records on created_at and get records for the days which have some records. i.e. excluding days with no record.
Enrollments.where(created_at: Time.current - 30.days..Time.current)
.group("date(created_at)")
.count
.sort
.to_h
Output Example
{Mon, 19 Nov 2018=>12}
answered yesterday
Uzair Nadeem
2081417
If you want to get count of all days including the days where there isn't any enrollment:
daily_enrollments =
i = 29
while i >= 0
daily_enrollments >> Enrollments.where( created_at: Time.zone
.now
.beginning_of_day-i.day..
Time.zone
.now
.end_of_day-i.day).count
i = i-1
end
Output Example
[0,0,0,5,4,0,0,0,5,0,23,0,0,23,0,0,1,0,11,0,21,32,44,0,0,9,6,3,22,1]
This will give you an array of all 30 days. But this isn't seems an efficient code. This will run 30 queries for each record.
What seems better to me is group the records on created_at and get records for the days which have some records. i.e. excluding days with no record.
Enrollments.where(created_at: Time.current - 30.days..Time.current)
.group("date(created_at)")
.count
.sort
.to_h
Output Example
{Mon, 19 Nov 2018=>12}
answered yesterday
Uzair Nadeem
2081417
answered yesterday
Uzair Nadeem
2081417
answered yesterday
Uzair Nadeem
2081417
answered yesterday
Uzair Nadeem
2081417
2081417
add a comment |
add a comment |
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372313%2factive-records-daily-count-array-w-r-t-created-at-or-updated-at%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
