Forum: Ruby on Rails Active Record SQL View Support for SQL Server - Is this supp

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
BrendanC (Guest)
on 2007-03-23 20:13
(Received via mailing list)
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
Tom W. (Guest)
on 2007-03-23 20:34
(Received via mailing list)
On 23/03/07, BrendanC <removed_email_address@domain.invalid> wrote:
>
> 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
Weverton G. (Guest)
on 2007-03-23 21:30
(Received via mailing list)
Hi,

I use this and it works fine.

2007/3/23, BrendanC <removed_email_address@domain.invalid>:
> using business objects etc.
> BrendanC
>
>
> >
>


--
Weverton G. de Morais
Tecnólogo em Redes de Comunicação
Desenvolvedor Delphi
Ball, Donald A Jr (Library) (Guest)
on 2007-03-23 21:44
(Received via mailing list)
> 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
Tom W. (Guest)
on 2007-03-26 12:13
(Received via mailing list)
On 23/03/07, Ball, Donald A Jr (Library) <removed_email_address@domain.invalid>
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 G. (Guest)
on 2007-03-26 15:14
(Received via mailing list)
What's plugin's name?? I want it.

2007/3/26, Tom W. <removed_email_address@domain.invalid>:
> > a benefit to actually making model objects for the views? How do you
> Tom
>
> >
>


--
Weverton G. de Morais
Tecnólogo em Redes de Comunicação
Desenvolvedor Delphi
Tom W. (Guest)
on 2007-03-26 16:28
(Received via mailing list)
On 26/03/07, Weverton G. <removed_email_address@domain.invalid> 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)
Craig D. (Guest)
on 2007-03-26 18:06
(Received via mailing list)
On Mar 26, 2007, at 8:27 AM, Tom W. wrote:

>   self.aggregates = {
> It will select all the aggregated columns you've defined, as well as
> SalaryReport.group_by(:country_id, :conditions => {:region_id =>
>     def group_by(*groups)
>       add_conditions!(sql, options[:conditions], scope)
>
>     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
Tom W. (Guest)
on 2007-03-26 18:28
(Received via mailing list)
> 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
This topic is locked and can not be replied to.