Ruby on Rails query for a given set of dates

Question

I am trying to write a query for my Rails API that will return the total number of hours of job applications that are accepted per month for a given set of dates.

Table: Job, JobApplication

Job has many JobApplication

JobApplication has a field status "SUCCESS" if matched

Start Date: Jul 16 2015

Is it possible to have it return {Jul 16: 100, Aug 16: 200, .... } ?

I am quite confused. Any help would be appreciated.

This query would give the number of successful Job Applications within a date range.

JobApplication.where(status: "SUCCESS").("created_at < ? ", Jul 16).("created_at < ? ", Aug 15)

JobApplication.job.duration would give the number of hours for that job.

I am not sure how to put them together and then loop through the dates.

My expect results is something like this:

{Jul 16: 100, Aug 16: 200, .... }

Hey what is your data and what you are expecting?? put some sample data?? and what you are tried?? — Vishal Jain
– Vishal Jain, Commented Apr 27, 2016 at 9:39

Vishal Jain · Accepted Answer · 2016-04-27 10:35:00Z

1

Hey you can try this way:

JobApplication.joins(:job).where(status: "SUCCESS").where(job_applications => {:created_at => [state_date..end_date]}).group(:created_at).sum("jobs.duration")

It will Return you result like

{Date => total_duration, :date => total_duration}

If You want you can convert date to specific format like DATE_FORMAT(created_at, "%d-%m-%y") so just put group(DATE_FORMAT(created_at, "%m-%y")). It will remove your time part from timestamp in mysql and returns data daywise

answered Apr 27, 2016 at 10:35

Vishal Jain

1,9401 gold badge12 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kelvin Over a year ago

it's giving me PG::InvalidDatetimeFormat: ERROR: invalid input syntax for type timestamp: "--- !ruby/range

Vishal Jain Over a year ago

you can used PG related datetime function

Pascal · Accepted Answer · 2016-04-27 10:02:24Z

0

Something like

data = JobApplication.where(status: 'SUCCESS').group(:start_date).select('start_date, count(*), sum(duration) as sum')

should do the trick, if i understood your question right. This will return JobApplication instances with sum set

Perhaps it is easier to run a pure SQL query:

SELECT start_date, count(*), sum(duration) FROM job_applications WHERE status LIKE 'SUCCESS' GROUP BY start_date

answered Apr 27, 2016 at 10:02

Pascal

8,6861 gold badge24 silver badges35 bronze badges

1 Comment

Kelvin Over a year ago

sorry i meant for a month, starting jul 16 to aug 15, not just for one day and duration is in the job table, do i need to include(job)?

Collectives™ on Stack Overflow

Ruby on Rails query for a given set of dates

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related