Greetings. I have an application which is used to track jobs and payments. The billables table contains columns "job_date", "amount" and "receipt" which indicate the date of the job, the amount due and whether or not payment has been received, respectively. In addition to tracking and manipulating individual jobs, the application is also used to generate aggregate information for periods of time, e.g., the sum for jobs in given months, quarters and years. I wrote the original version of the application in PHP with lots of direct SQL calls. When generating the aggregate reports, rather than selecting all the rows and then crunching numbers in PHP I created database views which returned the aggregate figures, e.g., the monthly view returned the year, month and monthly sum; the quarterly view returned the year, quarter and quarterly sum, etc. I am now reimplementing the application using rails. I have a Job model for working with the individual jobs. What might be the wisest approach for dealing with the aggregate reports? I have considered three options: 1) Create the same database views as before. Access these views directly using the Job.connection object and raw SQL. Coupling the database structure to the controller seems like a bad thing though. 2) Create the same database views as before. Create new models for each database view, e.g., a MonthlyReport model for accessing the monthly aggregate view. The models should be read-only by nature. Jobs are more complicated than I''ve explained, and the result is that the database has a lot of views and therefore a lot of models, but this should be manageable if I''m thoughtful of the namespace. 3) Discard the views altogether. Retrieve the relevant Jobs and generate all the aggregate information manually. Note that yearly aggregate information is frequently accessed, so the application could retrieve and process tens of thousands of Jobs at a time. Thanks for any advice you might have.
Check out ActiveRecord Calculations. On Sunday, April 23, 2006, at 9:48 AM, wrote:> Greetings. > > I have an application which is used to track jobs and payments. >The billables table contains columns "job_date", "amount" and "receipt" >which indicate the date of the job, the amount due and whether or not >payment has been received, respectively. In addition to tracking and >manipulating individual jobs, the application is also used to generate >aggregate information for periods of time, e.g., the sum for jobs in >given months, quarters and years. > > I wrote the original version of the application in PHP with >lots of direct SQL calls. When generating the aggregate reports, >rather than selecting all the rows and then crunching numbers in PHP >I created database views which returned the aggregate figures, e.g., >the monthly view returned the year, month and monthly sum; the quarterly >view returned the year, quarter and quarterly sum, etc. > > I am now reimplementing the application using rails. I have a >Job model for working with the individual jobs. What might be the >wisest approach for dealing with the aggregate reports? I have >considered three options: > > 1) Create the same database views as before. Access these views >directly using the Job.connection object and raw SQL. Coupling the database >structure to the controller seems like a bad thing though. > > 2) Create the same database views as before. Create new models for >each database view, e.g., a MonthlyReport model for accessing the >monthly aggregate view. The models should be read-only by nature. >Jobs are more complicated than I''ve explained, and the result is >that the database has a lot of views and therefore a lot of models, but >this should be manageable if I''m thoughtful of the namespace. > > 3) Discard the views altogether. Retrieve the relevant Jobs >and generate all the aggregate information manually. Note that yearly >aggregate information is frequently accessed, so the application could >retrieve and process tens of thousands of Jobs at a time. > > Thanks for any advice you might have. > >_______________________________________________ >Rails mailing list >Rails@lists.rubyonrails.org >http://lists.rubyonrails.org/mailman/listinfo/rails_Kevin -- Posted with http://DevLists.com. Sign up and save your mailbox.
Bloody marvelous. Thank you. Looking this over I am left with one new question which I couldn''t answer by reading the ActiveRecord::Calculations API docs or by experimenting: My original description was somewhat simplified. The model actually has three separate numerical columns on which aggregate calculations must be done. Can these three aggregate values be calculated with a single query, or must a separate query be done for each column? After reading about and experimenting with calculate() I couldn''t find a way to do this. Thanks again. On 23 Apr 2006 17:21:57 -0000, Kevin Olbrich <devlists-rubyonrails@devlists.com> wrote:> Check out ActiveRecord Calculations. > > On Sunday, April 23, 2006, at 9:48 AM, wrote: > > Greetings. > > > > I have an application which is used to track jobs and payments. > >The billables table contains columns "job_date", "amount" and "receipt" > >which indicate the date of the job, the amount due and whether or not > >payment has been received, respectively. In addition to tracking and > >manipulating individual jobs, the application is also used to generate > >aggregate information for periods of time, e.g., the sum for jobs in > >given months, quarters and years. > > > > I wrote the original version of the application in PHP with > >lots of direct SQL calls. When generating the aggregate reports, > >rather than selecting all the rows and then crunching numbers in PHP > >I created database views which returned the aggregate figures, e.g., > >the monthly view returned the year, month and monthly sum; the quarterly > >view returned the year, quarter and quarterly sum, etc. > > > > I am now reimplementing the application using rails. I have a > >Job model for working with the individual jobs. What might be the > >wisest approach for dealing with the aggregate reports? I have > >considered three options: > > > > 1) Create the same database views as before. Access these views > >directly using the Job.connection object and raw SQL. Coupling the database > >structure to the controller seems like a bad thing though. > > > > 2) Create the same database views as before. Create new models for > >each database view, e.g., a MonthlyReport model for accessing the > >monthly aggregate view. The models should be read-only by nature. > >Jobs are more complicated than I''ve explained, and the result is > >that the database has a lot of views and therefore a lot of models, but > >this should be manageable if I''m thoughtful of the namespace. > > > > 3) Discard the views altogether. Retrieve the relevant Jobs > >and generate all the aggregate information manually. Note that yearly > >aggregate information is frequently accessed, so the application could > >retrieve and process tens of thousands of Jobs at a time. > > > > Thanks for any advice you might have. > > > >_______________________________________________ > >Rails mailing list > >Rails@lists.rubyonrails.org > >http://lists.rubyonrails.org/mailman/listinfo/rails > > > _Kevin > > -- > Posted with http://DevLists.com. Sign up and save your mailbox. > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- John Parker jparker@pobox.com