BrendanC
2007-Mar-23 18:11 UTC
Active Record SQL View Support for SQL Server - Is this supported/possible?
I''m just exploring ROR for the first time - so excuse me if that has already been asked/answered. I''m intersted in developing a Web front end to an existing SQL Server database - migrating this to MySql/ Postgres is NOT an option. The front end needs to display data that is aggregated from multiple tables via SQL server views. The views already exist in the database and are sufficiently complex that I cannot consider recoding them using business objects etc. Is it possible to use a SQL Server view as the source for an Active Record - I want to create a form that displays the aggregrated data (represented by views). There in no requirement to update from these views. Any links/references/examples/tutorials would be helpful. TIA, BrendanC --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Tom Ward
2007-Mar-23 18:33 UTC
Re: Active Record SQL View Support for SQL Server - Is this supported/possible?
On 23/03/07, BrendanC <brencam-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > I''m just exploring ROR for the first time - so excuse me if that has > already been asked/answered. I''m intersted in developing a Web front > end to an existing SQL Server database - migrating this to MySql/ > Postgres is NOT an option. > > The front end needs to display data that is aggregated from multiple > tables via SQL server views. The views already exist in the database > and are sufficiently complex that I cannot consider recoding them > using business objects etc. > > Is it possible to use a SQL Server view as the source for an Active > Record - I want to create a form that displays the aggregrated data > (represented by views). There in no requirement to update from these > views.I do this all the time without any problems. Simply access the view as though it were a table. Tom --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Weverton Gomes
2007-Mar-23 19:27 UTC
Re: Active Record SQL View Support for SQL Server - Is this supported/possible?
Hi, I use this and it works fine. 2007/3/23, BrendanC <brencam-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > > I''m just exploring ROR for the first time - so excuse me if that has > already been asked/answered. I''m intersted in developing a Web front > end to an existing SQL Server database - migrating this to MySql/ > Postgres is NOT an option. > > The front end needs to display data that is aggregated from multiple > tables via SQL server views. The views already exist in the database > and are sufficiently complex that I cannot consider recoding them > using business objects etc. > > Is it possible to use a SQL Server view as the source for an Active > Record - I want to create a form that displays the aggregrated data > (represented by views). There in no requirement to update from these > views. > > > Any links/references/examples/tutorials would be helpful. > > TIA, > BrendanC > > > > >-- Weverton Gomes de Morais Tecnólogo em Redes de Comunicação Desenvolvedor Delphi --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ball, Donald A Jr \(Library\)
2007-Mar-23 19:42 UTC
Re: Active Record SQL View Support for SQL Server - Is thissupported/possible?
> I do this all the time without any problems. Simply access > the view as though it were a table.I do this for aggregate statistics views by choosing the model most closely tied to the view and calling find_by_sql on its class. Is there a benefit to actually making model objects for the views? How do you cope with views that do not have primary keys? Alternately, is there a benefit to getting the DBI connection and calling query manually instead of going through find_by_sql? - donald --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Tom Ward
2007-Mar-26 08:12 UTC
Re: Active Record SQL View Support for SQL Server - Is thissupported/possible?
On 23/03/07, Ball, Donald A Jr (Library) <donald.ball-GjtI+QwuxAR68HQyEA6aog@public.gmane.org> wrote:> > > I do this all the time without any problems. Simply access > > the view as though it were a table. > > I do this for aggregate statistics views by choosing the model most > closely tied to the view and calling find_by_sql on its class. Is there > a benefit to actually making model objects for the views? How do you > cope with views that do not have primary keys?As long as you''re not doing updates, the lack of primary keys isn''t an issue. Using a model object gives the adapter the opportunity to type cast the returned values consistently. It also makes it easier to auto-generate aggregate queries (avoiding find_by_sql). As an example, I use a plugin that allows SalaryReport.group_by(:department_id) and similar. I can share if you''re interested. Tom --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Weverton Gomes
2007-Mar-26 11:13 UTC
Re: Active Record SQL View Support for SQL Server - Is thissupported/possible?
What''s plugin''s name?? I want it. 2007/3/26, Tom Ward <tom-FF2hOUnQ1ZmsTnJN9+BGXg@public.gmane.org>:> > > On 23/03/07, Ball, Donald A Jr (Library) <donald.ball-GjtI+QwuxAR68HQyEA6aog@public.gmane.org> > wrote: > > > > > I do this all the time without any problems. Simply access > > > the view as though it were a table. > > > > I do this for aggregate statistics views by choosing the model most > > closely tied to the view and calling find_by_sql on its class. Is there > > a benefit to actually making model objects for the views? How do you > > cope with views that do not have primary keys? > > As long as you''re not doing updates, the lack of primary keys isn''t an > issue. Using a model object gives the adapter the opportunity to type > cast the returned values consistently. It also makes it easier to > auto-generate aggregate queries (avoiding find_by_sql). As an > example, I use a plugin that allows > SalaryReport.group_by(:department_id) and similar. I can share if > you''re interested. > > Tom > > > >-- Weverton Gomes de Morais Tecnólogo em Redes de Comunicação Desenvolvedor Delphi --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Tom Ward
2007-Mar-26 12:27 UTC
Re: Active Record SQL View Support for SQL Server - Is thissupported/possible?
On 26/03/07, Weverton Gomes <weverton-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> What''s plugin''s name?? I want it.I''m not able to officially release it as I don''t have the time to support it. I can provide the (very scrappy) code if you want to play around with it, at your own risk! Usage is pretty basic. First, define model with some aggregates: class SalaryReport < ActiveRecord::Base self.aggregates = { :salary => ''sum(salary)'', :average_salary => ''avg(salary)'' } end Then, call group_by(columns) to get something you can turn into a report: SalaryReport.group_by(:country_id, :department_id) It will select all the aggregated columns you''ve defined, as well as the grouped attributes, so the example above is similar to SELECT country_id, department_id, sum(salary) salary. avg(salary) average_salary FROM salary_report GROUP BY country_id, department_id You can also include conditions if you wish, similar to those for the find method: SalaryReport.group_by(:country_id, :conditions => {:region_id => ''EUROPE''}) Here''s the actual code: module Tomafro::ActiveRecord::GroupBy def self.included(base) base.extend(ClassMethods) end module ClassMethods def group_by(*groups) groups = groups.dup.flatten options = groups.last.is_a?(Hash) ? groups.pop : {} scope = scope(:find) aggregate_columns = self.aggregates.collect do |key, value| "#{value} #{key}" end group_columns = groups.collect(&:to_s) sql = "SELECT #{(group_columns + aggregate_columns).join(",\n ")} " sql << "FROM #{table_name} " add_conditions!(sql, options[:conditions], scope) sql << " GROUP BY #{group_columns.join(", ")}" sql << " WITH ROLLUP" if options[:rollup] records = find_by_sql(sql) records.each { |record| record.readonly! } records end def aggregates @aggregates || {} end def aggregates=(aggregates) @aggregates = aggregates end end end ActiveRecord::Base.send(:include, Tomafro::ActiveRecord::GroupBy) --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Craig Demyanovich
2007-Mar-26 14:03 UTC
Re: Active Record SQL View Support for SQL Server - Is thissupported/possible?
On Mar 26, 2007, at 8:27 AM, Tom Ward wrote:> > On 26/03/07, Weverton Gomes <weverton-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> What''s plugin''s name?? I want it. > > I''m not able to officially release it as I don''t have the time to > support it. I can provide the (very scrappy) code if you want to play > around with it, at your own risk! Usage is pretty basic. First, > define model with some aggregates: > > class SalaryReport < ActiveRecord::Base > self.aggregates = { > :salary => ''sum(salary)'', > :average_salary => ''avg(salary)'' > } > end > > Then, call group_by(columns) to get something you can turn into a > report: > > SalaryReport.group_by(:country_id, :department_id) > > It will select all the aggregated columns you''ve defined, as well as > the grouped attributes, so the example above is similar to > > SELECT country_id, department_id, sum(salary) salary. avg(salary) > average_salary > FROM salary_report > GROUP BY country_id, department_id > > You can also include conditions if you wish, similar to those for the > find method: > > SalaryReport.group_by(:country_id, :conditions => {:region_id => > ''EUROPE''}) > > Here''s the actual code: > > module Tomafro::ActiveRecord::GroupBy > def self.included(base) > base.extend(ClassMethods) > end > > module ClassMethods > def group_by(*groups) > groups = groups.dup.flatten > options = groups.last.is_a?(Hash) ? groups.pop : {} > scope = scope(:find) > aggregate_columns = self.aggregates.collect do |key, value| > "#{value} #{key}" > end > group_columns = groups.collect(&:to_s) > sql = "SELECT #{(group_columns + aggregate_columns).join(", > \n ")} " > sql << "FROM #{table_name} " > add_conditions!(sql, options[:conditions], scope) > sql << " GROUP BY #{group_columns.join(", ")}" > sql << " WITH ROLLUP" if options[:rollup] > records = find_by_sql(sql) > records.each { |record| record.readonly! } > records > end > > def aggregates > @aggregates || {} > end > > def aggregates=(aggregates) > @aggregates = aggregates > end > end > end > > ActiveRecord::Base.send(:include, Tomafro::ActiveRecord::GroupBy)I just read about ActiveRecord::Calculations::ClassMethods this morning. Tom, do the grouping capabilities provided by those methods not meet your needs such that you wrote this plugin for yourself? Thanks, Craig --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Tom Ward
2007-Mar-26 14:26 UTC
Re: Active Record SQL View Support for SQL Server - Is thissupported/possible?
> I just read about ActiveRecord::Calculations::ClassMethods this > morning. Tom, do the grouping capabilities provided by those methods > not meet your needs such that you wrote this plugin for yourself?Last I looked, the Calculations stuff could only pull out a single aggregate at a time. i.e to find total payroll and average salary would take two queries: SalaryReport.sum(:salary, :conditions => {:country_id => ''DE''}) SalaryReport.avg(:salary, :conditions => {:country_id => ''DE''}) Of course, it''s also possible to get these results using the standard finder: SalaryReport.find(:all, :select => ''sum(salary) total_salary, avg(salary) avg_salary'' .......) I''m not advocating my code over either of these techniques. For my particular purpose, where I''m pulling several reports down from views, each of which can be grouped in an astounding number of ways, my little piece of code has proved useful. For general grouping code, you may well find the existing finder methods sufficient. Tom --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---